SQL Myth: Primary Key and Clustered Index

After remaining quiet for almost a year I’m back with what I enjoy the most, talking about SQL and sharing whatever little knowledge I have.   Many SQL Developers have this misconception:  “Primary key => Clustered Index: Only a Clustered Index can exist on a Primary key column”.     On numerous occasions I had tough times explaining that this is not the case every time, you can create a Non-Clustered Index on a primary key column.  But if this hot discussion is going on across a coffee table and I’m away from Computer I get helpless.  So finally I decided to write about this.

You can create a Non-Clustered Index on primary key column.   Or if I try to put this in Myth Buster words “A primary key column can exist/survive without a Clustered Index”  Yes it is a fact that PRIMARY KEY constraints default to CLUSTERED Index.   But it doesn’t mean that you CAN’T create a non-clustered index on a Primary Key Column.  And also you can create a Clustered Index on a non-primary key column.  Let me show you this with some simple examples. 

Case 1:  1st lets see what happens when you specify only PRIMARY KEY and nothing else.  In this case YES, by default Clustered Index will be created on Primary Key Column.

USE tempdb GO CREATE TABLE MyTable1 ( Id INT PRIMARY KEY, Dates DATETIME ) GO

You can see as expected a clustered Index got created on ID column.

Case 2:  But by just adding a NONCLUSTERED word in front of primary key you can tell SQL Server to create a Non Clustered Index instead of a default Clustered one.

Here you go:

USE tempdb GO CREATE TABLE MyTable2 ( Id INT PRIMARY KEY NONCLUSTERED, Dates DATETIME ) GO

As you can clearly see in the image a non-clustered index got created on ID column which is also a primary key.

Case 3:  Now here is the small trick, you can force SQL Server to create a non-clustered index on a primary key column even without writing NONCLUSTERED in front of it.  Yes, there is exception to the rule   “PRIMARY KEY constraints default to CLUSTERED Index” even if you don’t specify NONCLUSTERED.   Question is, How?  Well by simply creating a Clustered Index on another column while creating the table.

USE tempdb GO CREATE TABLE MyTable3 ( Id INT PRIMARY KEY, Dates DATETIME UNIQUE CLUSTERED ) GO

See the Image, a Non Clustered got created on ID column and a Clustered on Dates column.

The obvious question will be, why SQL Server didn’t create the Clustered on Id column this time?  Answer is very simple, if you know the basic rule “You can have only one Clustered Index on a table”.  And since in the CREATE statement you forced SQL Server to create a clustered index on dates columns SQL Server had no choice but to create a non clustered on Id column.

What we learned today?
Honestly speaking, I didn’t tell anything new.  Experts/people with good knowledge about SQL Server already knew this.  But interesting thing we can learn here – yes there are some DEFAULTs set by SQL Server, but that doesn’t stop you from telling SQL Server “Boss enough of your DEFAULTs, now let me take the control”.  Actually in early days of learning SQL we all get into  this habit of relying on DEFAULTs set by SQL Server.  And we get so used to them that we start considering them as RULES that can’t be broken.

Actually I think there is no harm in taking little bit extra effort and writing some extra keywords and telling SQL Server this is what I want or this is what is expected.

You also learned how to create a Clustered Index on a column of your choice.  This can be very useful when you don’t want a Clustered Index on a primary key especially in cases like where you are using GUID as a primary key(I hate them) and you want Clustered Index to be created on some other column.

Namaste!
- Mangal.