On Primary key and Clustered indexes

There are so many articles around the net about this topic. Great DBAs have written a lot on this already. Still I see a number of myths regarding clustered index going around among newbies. In fact some idea held by a good friend-colleague made me write this entry.

Following are the myths many people hold

Myth #1) Clustered indexes are always primary key or vice versaIt is true that when we create a primary key on a table by default a clustered index is created. But definitely we can opt for a non clustered index if we have a reason to do so.

Eg. We create a table as

CREATE TABLE ClusterMythBuster
(
PkColumn INT NOT NULL,
Column1 VARCHAR(10),
Column2 VARCHAR(25),
EgFiltercol VARCHAR(10),

)

Now the table is a heap (Meaning there is no clustered index, and no physical order in which the data is stored in the table)
Now I will create a primary key that is not a clustered index


ALTER TABLE ClusterMuthBuster ADD CONSTRAINT Pk_ ClusterMythBuster  PRIMARY KEY NONCLUSTERED (PkColumn)

Now let us add a clustered index to this table to a non key column


CREATE CLUSTERED INDEX IX_ ClusterMythBuster_EgFiltercol ON  ClusterMythBuster  (EgFiltercol)

Now we have a clustered index that is not a primary key as well as a primary key that is not a clustered index.


Myth#2) Clustered index cannot be created on non unique column.This is also not true. This already evident from the earlier example, still we can seal it by trying to insert some value into the above table.


insert into  ClusterMythBuster values (1,'Test1','Test2',1)
insert into  ClusterMythBuster values (2,'Test1','Test2',1)
insert into  ClusterMythBuster values (3,'Test1','Test2',1)
insert into  ClusterMythBuster values (4,'Test1','Test2',2)
insert into  ClusterMythBuster values (5,'Test1','Test2',2)
insert into  ClusterMythBuster values (6,'Test1','Test2',1)

See that now since the clustered index is created on EgFiltercol column, the data will be ordered as first all the ones and then the twos.

Please note: It is always recommended to create a clustered index on a column that is ever increasing, since it will create less fragmentation.


Myth#3 A clustered index cannot be created on a combination of columnsThis is also not true since we can create a composite clustered index.

See the example below

First we need to drop the existing since we can have only one clustered index per table

DROP INDEX  ClusterMythBuster .IX_ClusterMythBuster_EgFiltercol


CREATE CLUSTERED INDEX IX_ClusterMythBuster_CompCluster ON  ClusterMythBuster (Column1,Column2)


However, I would like to end by saying that ironically all the above myths are serving one purpose since it is always safe to choose a column that is unique and ever increasing. It is also better if the clustered index is narrow (less number of columns),
But my point is that if you have your own valid reasons to choose so, you can opt otherwise.

Please see these links

http://www.sqlservercentral.com/articles/Indexing/68563/
http://msdn.microsoft.com/en-us/library/ms190639.aspx
http://msdn.microsoft.com/en-us/library/ms177443.aspx
http://msdn.microsoft.com/en-us/library/aa933131(v=sql.80).aspx