posted 5/3/2012 by pkumar3 - Views: [3500]
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:
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.
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.