Tags

, ,


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