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,
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