SQL SERVER – Primary Key and NonClustered Index in Simple Words

I have been writing a weekly round up from my blog where I go over last six years of blog posts and pick the best posts from the pasts. While I do this, there are two major place where I focus 1) If there are change in features – I re-blog about it with additional details or 2) If I have not provided complete information six years ago, I try to fill up the gap now. Well, just like everything my knowledge and writing skills have evolved. Before continuing please read my latest memory lane blog post where in 2007 I wrote scripts for Primary Key and Unique Key.

November 2006 was when I started to learn more about SQL and have been only 4 months in the product, I was still exploring various subjects. I wrote a blog post describing about how Primary Key and Unique Key are different. Everything which I wrote there is correct, however, there are a few more details one should learn when it is about Primary Key and Clustered Index.

Here is the common misconception prevailing in the industry.

Primary Key has to be Clustered Index. 

In reality the statement should be corrected as follows:

Primary Key can be Clustered or Non-clustered but it is a common best practice to create a Primary Key as Clustered Index. 

Well, now we have corrected the statement let us understand a bit more in detail. Primary Key should be uniquely identifying column of the table and it should be NOT NULL. A good (most of the time) candidate of the clustered index key also uniquely identifies column and NOT NULL (most of the time). Well, that means it is a good idea to create a Primary Key Clustered so it solve both the problems together. Keeping these facts in mind SQL Server automatically creates Clustered Index on the Primary Key when the table is created. Developers often do not specify which column should have clustered index so by default Primary Key becomes Clustered Index. This practice is now extremely common and lots of people have forgotten that Primary Key and Clustered Index is two different things. They can be same column but they do not have to be.

Well here are four examples we will see where we will learn the behavior of SQL Server when it is about Primary Key and Clustered Index.

  • Scenario 1 : Primary Key will default to Clustered Index
  • Scenario 2: Primary Key is defined as a Non-clustered Index
  • Scenario 3: Primary Key defaults to Non-Clustered Index with another column defined as a Clustered Index
  • Scenario 4: Primary Key defaults to Clustered Index with other index defaults to Non-clustered index

Now let us see each of the scenarios in detail.

Scenario 1 : Primary Key will default to Clustered Index

In this case we will create only Primary Key and when we check the kind of index created on the table we will notice that it has created clustered index automatically over it.

-- Case 1 Primary Key Defaults to Clustered Index
USE TempDB
GO
-- Create table
CREATE TABLE TestTable
(ID INT NOT NULL PRIMARY KEY,
Col1 INT NOT NULL)
GO
-- Check Indexes
SELECT OBJECT_NAME(OBJECT_ID) TableObject,
[name] IndexName,
[Type_Desc] FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TestTable'
GO
-- Clean up
DROP TABLE TestTable
GO

SQL SERVER - Primary Key and NonClustered Index in Simple Words  clpk1

Scenario 2: Primary Key is defined as a Non-clustered Index

In this case we will explicitly defined Primary Key as a non-clustered index and it will create it as a non-clustered index. It proves that Primary Key can be non-clustered index.

-- Case 2 Primary Key Non-clustered Index
USE TempDB
GO
-- Create table
CREATE TABLE TestTable
(ID INT NOT NULL PRIMARY KEY NONCLUSTERED,
Col1 INT NOT NULL)
GO
-- Check Indexes
SELECT OBJECT_NAME(OBJECT_ID) TableObject,
[name] IndexName,
[Type_Desc] FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TestTable'
GO
-- Clean up
DROP TABLE TestTable
GO

SQL SERVER - Primary Key and NonClustered Index in Simple Words  clpk2

Scenario 3: Primary Key defaults to Non-Clustered Index with another column defined as a Clustered Index

In this case we will create clustered index on another column, SQL Server will automatically create a Primary Key as a non-clustered index as clustered index is specified on another column.

-- Case 3 Primary Key Defaults to Non-clustered Index
USE TempDB
GO
-- Create table
CREATE TABLE TestTable
(ID INT NOT NULL PRIMARY KEY,
Col1 INT NOT NULL UNIQUE CLUSTERED)
GO
-- Check Indexes
SELECT OBJECT_NAME(OBJECT_ID) TableObject,
[name] IndexName,
[Type_Desc] FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TestTable'
GO
-- Clean up
DROP TABLE TestTable
GO

SQL SERVER - Primary Key and NonClustered Index in Simple Words  clpk3

Scenario 4: Primary Key defaults to Clustered Index with other index defaults to Non-clustered index

In this case we will create two indexes on the both the tables but we will not specify the type of the index on the columns. When we check the results we will notice that Primary Key is automatically defaulted to Clustered Index and another column as a Non-clustered index.

-- Case 4 Primary Key and Defaults
USE TempDB
GO
-- Create table
CREATE TABLE TestTable
(ID INT NOT NULL PRIMARY KEY,
Col1 INT NOT NULL UNIQUE)
GO
-- Check Indexes
SELECT OBJECT_NAME(OBJECT_ID) TableObject,
[name] IndexName,
[Type_Desc] FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TestTable'
GO
-- Clean up
DROP TABLE TestTable
GO

SQL SERVER - Primary Key and NonClustered Index in Simple Words  clpk4

I think above examples clarifies if there are any confused related to Primary and Clustered Index.

Now here is the question I often get asked what can be the reason for creating Primary Key and Clustered Index Key on different columns. Well, there are many scenarios when this can be true. It is possible that you have column SSN which you want to create as a Primary Key but do not want make it as a clustered index key because you have uniquely increasing identity column which best suites your need for that table (again this is just an example – you can argue exactly the opposite way as well). You are welcome to continue the discussion on this subject in the comments field or a dedicated blog post I wrote about it years ago over here. There are few really good comments there – I think that blog post is now a gold mine to understand this concept.

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Weekly Series – Memory Lane – #015
Next Post
SQL SERVER – What is Semantics Model – A Simple Explanation

Related Posts

41 Comments. Leave new

  • Hi,
    On a table l have cluster index, over a period of time I have to modify or change or so maintenance of this table

    Reply
  • Excellent

    Reply
  • Question 1:
    You have a vending machine that can deliver a bubble gum to customer when he/she put coins worth of Rs. 15 in it. The vending machine’s single coin slot can accept coins of Rs. 5 and Rs. 10 only. So, when a customer enters coins worth of Rs. 15 or more then vending machine will be open and deliver the bubble gum to customer and will be reset again.
    Now, you are required to:
    Design a state diagram for the above scenario
    Perform the process of state reduction and design a Moore machine
    kindly tell me how to handle this situation…..as soon as possible thanks…..

    Reply
  • Sameer Vishawakarma
    October 17, 2015 6:38 pm

    It’s my question….what happen if there is no primary key and foreign key in sql server 2008 or other database designs?

    If any know about it please reply

    thanks

    Reply
  • I have a status (INT) column which has distinct values. its used frequently in where clause for searching …also this column gets frequently updated…..i need to index it….i think it should be non clustered…pls advise.

    Reply
    • how many distinct values it has? by the name “Status” it doesn’t look like it would have many distinct values. What kind of queries are hitting this table other than updates. There would be a primary key on that table?

      Reply
  • There can be a situation where you have ALTERNATE KEY IN TABLE,then we may define one column as PK and another alternate key as CI .BUT BIG QUESTION,what is the advantage of I am getting by doing this ?Anybody ?

    Reply
  • Hello Sir,

    Can you please help me on below.

    I have a scenario, I created partitions on table and configure aligned partition clustered index on the same. table partitions belongs to multiple different file group , how i can create non partitioned clustered index on same as all partitions. belongs to multiple file group.

    Please suggest.

    Thank you

    Reply
  • Excellent post. Also solved my confusion related to index

    Keep going.

    Thank you Pinal

    Reply
  • Excellent Pinal! Everytime I search something on google related to sql server… at least 80% of the time I end up to your blog, even more than stackoverflow… thanks a lot!

    Reply
  • Hi Pinal May I know the use of clusterd index and non-clustred index?? Have you written any blog ??

    Reply
  • Haynes Rajwadi
    June 27, 2018 3:44 pm

    Hi Pinal, thank you explaining all 4 scenarios to differentiate Clustered and Unique Index. Question to you, probably I might be deviated from the topic, this is regarding Scenario 2: Primary Key is defined as a Non-clustered Index, the snapshot suggests the PK as Non-clustered index is created on HEAP storage.
    Is it true, if the UQ (Unique key) defined as Non-clustered is also created on same media or is it Stack based? If UQ is created on HEAP how is NULL value interpreted while performing scan on SQL statements accessing Unique key?

    Reply
  • Generally what is the reason for making primary key in sql as nonclustered index? any specific advantage or benefit or Performance improvement

    Reply

Leave a Reply

Menu