Why the Clustered Index should be Unique In SQL Server

Introduction

CREATE TABLE [DBO].[NotUniqueCI] (
Primarykey int NOT NULL , 
Keycol VARCHAR(50) NOT NULL
,SearchCol INT
,SomeData char(20),

Keycol2 VARCHAR(50) NOT NULL
,SearchCol2 INT
,SomeData2 char(20),


Keycol3 VARCHAR(50) NOT NULL
,SearchCol3 INT
,SomeData3 char(20) )




INSERT INTO [DBO].[NotUniqueCI]
SELECT 
n ,

n,
n
,'Some text..'
,
n,
n
,'Some text..'

,

n,
n
,'Some text..'

FROM Numbers
GO

--ALTER TABLE DBO.[NotUniqueCI]
--ADD CONSTRAINT PK_NotUniqueCI PRIMARY KEY (Primarykey)

CREATE CLUSTERED INDEX  CI_NotUniqueCI ON  [DBO].[NotUniqueCI] (Primarykey) 


Create nonclustered index NC_NotUniqueCI_Keycol on [NotUniqueCI](Keycol,SearchCol,SearchCol2)

Create nonclustered index NC_NotUniqueCI_SearchCol on [NotUniqueCI](SearchCol,Keycol,SomeData)

Create nonclustered index NC_NotUniqueCI_SomeData on [NotUniqueCI](SomeData,Keycol,SearchCol)

Create nonclustered index NC_NotUniqueCI_Keycol2 on [NotUniqueCI](Keycol2,SearchCol,SearchCol2)

Create nonclustered index NC_NotUniqueCI_SearchCol2 on [NotUniqueCI](SearchCol2,Keycol,SomeData)

Create nonclustered index NC_NotUniqueCI_SomeData2 on [NotUniqueCI](SomeData2,Keycol,SearchCol)


----------This index is creted in the previous part of the series

CREATE INDEX NC_NotUniqueCI_SearchCol3 on NotUniqueCI(SearchCol3 )

Now let us see all the indexes on the both tables using SP_HELP command:

SP_HELP 'IdealCI'
GO
SP_HELP 'NotUniqueCI'
GO

Both tables have identical table structure, indexes and number of records, but there is just one slight difference in the both tables which is ideal table has a unique clustered index  and it is not unique in the Nonunique table, so let us check the total number of pages in the both tables using the sys.dm_db_index_physical_stats DMV.

SELECT SUM (Page_Count *8 ) Space_Used_KB

FROM sys.dm_db_index_physical_stats 
(db_id('TutorialSQLServer'), object_id ('IdealCI' ), null ,null,'DETAILED')
GO
SELECT SUM (Page_Count *8 ) Space_Used_KB

FROM sys.dm_db_index_physical_stats 
(db_id('TutorialSQLServer'), object_id ('NotUniqueCI' ), null ,null,'DETAILED')

First penalty just not for creating a unique clustered index is costing an additional 928 KB of wasted space in a small table which contain only 500000 rows, so we need to look inside what is Extra in the NotUniqueCI table, doing this we will first use DBCC IND Command for the clustered index.

DBCC IND ('TutorialSQLServer', 'NotUniqueCI', 1);

Please note this is not the output we got from the above command I rearranged the columns and removed some so the output of the above command become more meaningful, as we already know how to identify the root level page id from the above output by looking the maximum index level, which would have previous and next page id is zero. If you are new to these things we strongly recommended to visit this article to get a better understanding on SQL Server internal of indexes.

So now we know the page id of the clustered index let`s look inside the page.

DBCC PAGE ('TutorialSQLServer',1,158322,3) WITH TABLERESULTS

DBCC PAGE ('TutorialSQLServer',1, 158256,3) WITH TABLERESULTS

DBCC IND ('TutorialSQLServer', 'NotUniqueCI', 8);

DBCC PAGE ('TutorialSQLServer',1,242904,3) WITH TABLERESULTS – data page

Now the more important question is why a clustered index has to be unique?

The motive behind this is all the non clustered index rows has a link to the clustered index rows in one to one manner relationship so it could seek the matching Row  in the clustered index, this one to one relationship will make sure every non clustered index will link to only one row in the table.

Let us see the practical example using the simple query against the SearchCol3 column and look at the execution plan:

SELECT * From NotUniqueCI where SearchCol3=1

Look at the above execution plan, to fulfill the above query optimally the Query Optimizer has chosen the search3 non clustered index because it is indexed in the SearchCol3 column, but the user has requested all the column and this index does not contain all the requested columns so the Query Optimizer has to look into the Clustered Index for the rest of the columns using the clustered index key and Uniquifier in the clustered index to confirm that it will maintain one to one relationship, look in the output section of  the outer part of the Nested Loop Join (by the way I am writing a series of articles on the  Nested Loop Join iterator if you  are intereseted in knowing about it you should look into this), there are three column in the output searchcolumn , primarykey and Unq1002.

This unq1002 column is actually Uniquifier, which required by the Query Optimzer to join the Clustered Index along with a primarykey column.

So in the summary what the Query Optimizer is doing:

STEP 1:  Seek for the SearchCol3 column 3 in the  non clustered index.

STEP 2:  Get a unique identifier  column and primary key along with SearchCol3 column.

STEP 3: Then seek for the Uniquifier and primary key column in the clustered index using the Nested Loop Join iterator (why they called iterator) and fetched all the remaining column from the clustered index right.

Just to add some evidence of  the 4 bytes theory of the Uniquifier column, we will query the NotUniqueCI and ideal table and look the difference in data flow estimation in the execution plan.

SELECT * FROM NotUniqueCI WHERE SearchCol3=1
GO
SELECT * FROM IdealCI WHERE SearchCol3=1

Look how 4 bytes and Uniquifier is extra  in the comparison to the ideal clustered index.

Duplicate Values in the Clustered Index

The clustered index is not unique, so let us add some duplicate values in the key column of the clustered index and see the effect on the both clustered and non clustered index. 

In the below query we will add 100 duplicate rows in the table:

DBCC PAGE ('TutorialSQLServer',1,158256,3) WITH TABLERESULTS 

Look the image above, we are looking at the last rows of the Clustered Index first data page, to make the clustered index unique how storage engine has used the Uniquifier +1 with every row and this would have updated to all the non clustered index as well, so let us look into it.

DBCC PAGE ('TutorialSQLServer',1,242904,3) WITH TABLERESULTS 

Here as well look at new rows inserted in this page and Uniquifier added as "+1" and this would happen in all non clustered index as well.

Whenever a duplicate value inserted in the non clustered index, storage engine will add +1 to the Uniquifier to make the row unique.

Ok, so let us delete all the data, duplicate rows and make all the index in the previous state by rebuilding the table.

DELETE from  [DBO].[NotUniqueCI] where primarykey =1
GO

INSERT INTO [DBO].[NotUniqueCI]
SELECT 
n ,

n,
n
,'Some text..'
,
n,
n
,'Some text..'

,

n,
n
,'Some text..'

FROM Numbers where n=1
GO
 ALTER TABLE [DBO].[NotUniqueCI] REBUILD
GO

Let's look at the page count in the index.

 

SELECT SUM (Page_Count *8 ) Space_Used_KB

FROM sys.dm_db_index_physical_stats 
(db_id('TutorialSQLServer'), object_id ('NotUniqueCI' ), null ,null,'DETAILED')

Wait, what happened as before inserting the rows the page count in the NotUniqueCI was 178080, now even we have deleted all the data and rebuild the index the claim the space?

Actually storage engine believes that the duplicate data might come back. 

So let us make the primary key column, Unique Clustered Index and claim all the wasted space and even it will remove the Uniquifier column.

To do that, we will first drop the existing Clustered Index then create a new one with a unique clustered index then we will check the data pages in the index.

DROP INDEX  CI_NotUniqueCI ON  [DBO].[NotUniqueCI] 
GO
CREATE UNIQUE CLUSTERED INDEX  CI_Uniquepk ON  [DBO].[NotUniqueCI] (Primarykey) 
GO
ALTER TABLE [DBO].[NotUniqueCI] REBUILD
GO

Let us again count the data pages, but this time we will compare the size of data pages to IDEALCI table 

as well.

 

SE

LECT SUM (Page_Count *8 ) Space_Used_KB

FROM sys.dm_db_index_physical_stats 
(db_id('TutorialSQLServer'), object_id ('NotUniqueCI' ), null ,null,'DETAILED')

GO
SELECT SUM (Page_Count *8 ) Space_Used_KB

FROM sys.dm_db_index_physical_stats 
(db_id('TutorialSQLServer'), object_id ('IDEALCI' ), null ,null,'DETAILED')

Great!! We have not just only claimed all the wasted space from the index, but now the index is much thinner  and size is even equivalent to the our IDEALCI table :)

So look for potential unique index and convert it into unique, don't forget make an index unique won`t allow duplicated data on the Unique key column.