I have heard this many times that Clustered Indexes are created only on columns that are defined as Primary Key or Unique Key. Clustered Indexes cannot be created on Columns that are having duplicate values.
Well, this is not true.
When we create any Primary keys then Clustered Indexes are created by default and we can have only one Clustered Index per table whereas we can create as many as 249 Non-Clustered Indexes on a single table. The key difference between Clustered and non-clustered index is the leaf level of the index. In non-clustered index, the leaf level contains pointers to the data. And in a Clustered index, the leaf level of the index is the actual data.
Today I am going to show you that we can create Clustered Indexes on columns having duplicate values. When clustered indexes are created on columns that have duplicate values, SQL Server generates an internal number to uniquely identify duplicate clustered index keys.
— I am creating a dummy table to hold the test data
CREATE TABLE dbo.Test
(
FName VARCHAR(20),
LName VARCHAR(20)
)
— Inserting duplicate records in the table created above
INSERT INTO test VALUES (‘Dan’ , ‘Allard’)
INSERT INTO test VALUES (‘Dan’ , ‘Allard’)
INSERT INTO test VALUES (‘Dan’ , ‘Allard’)
INSERT INTO test VALUES (‘Sam’ , ‘Philips’)
INSERT INTO test VALUES (‘Sam’ , ‘Philips’)
INSERT INTO test VALUES (‘Sam’ , ‘Philips’)
— Selecting the values from the dummy table
SELECT * FROM test
— Creating Clustered Index on Fname column of Test Table that is having duplicate records
CREATE CLUSTERED INDEX idx_test_FName ON Test(FName)
— It shows the following message
Thus, we can see that we can indeed create Clustered Index on a column that is having duplicate records.
Keep coding and Keep Learning!!
Thanks
Subhro Saha
Kevin Kline said:
You must be confused. Clustered indexes have no problem whatsoever with duplicate values. PRIMARY KEY columns and UNIQUE CONSTRAINT columns are the only columns that do not allow duplicate values. FWIW, defining a pk defaults to a clustered index. But the two are not synonymous. -Kevin
jhsj said:
thanks for the info…it really helps
mujju said:
Clustered Index will allow duplicates but the Index Key Positions are unique