Interesting one today:
For the last few months, on and off, there have been opportunities to run some interesting tests in our lab environment. this resulted in some good posts in the last few weeks. Adding to that tradition is another interesting topic Uniquifier.
Imagine a table with multiple records and a Clustered and bunch of NonClustered indexes. In the non-clustered indexes, the b-tree is structured based on the index keys and at the bottom of the tree, the leaf points back to clustered index using Clustering Key. Now imagine the same scenario with a non-unique clustered index, so there could be multiple records with same clustering key values. The dependent, non-clustered indexes now will need a way to uniquely identify between the identical looking entries. Enter Uniquifier column !!
An extra 4-byte column called uniquifier is added to all non-clustered indexes to uniquely distinguish between multiple index entries that result in pointing to the same clustering key.
Let’s take an example:
We’ll re-use some of the code from previous posts for this.
-- -- Create a dummy table to test DBCC IND -- IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Uniquifier_Test' AND type = 'U') DROP TABLE dbo.Uniquifier_Test GO CREATE TABLE dbo.Uniquifier_Test ( ID INT NOT NULL DEFAULT (1) , Name VARCHAR(5) NOT NULL DEFAULT('aa') ) GO -- -- Create Clustered and NonClustered indexes -- CREATE CLUSTERED INDEX CI_Uniquifier_Test_ID ON dbo.Uniquifier_Test(ID ASC) GO CREATE NONCLUSTERED INDEX nCI_Uniquifier_Test_Name ON dbo.Uniquifier_Test(Name ASC) GO -- -- Let's insert some dummy records -- INSERT INTO dbo.Uniquifier_Test (ID, Name) VALUES (1, 'aa') , (2, 'bb') , (3, 'cc') , (4, 'dd') GO SELECT * FROM dbo.Uniquifier_Test GO
-- -- Retrieve PageID of nCI -- DBCC IND (test, Uniquifier_Test, -1) GO -- -- Retrieve contents of nCI page -- DBCC TRACEON (3604) DBCC PAGE(test, 1, 34535, 3) GO
As you can see, along with the nCI key column, Name, we also have the clustering key (ID) added to the nCI b-tree structure. Along with that there is a new column called UNIQUIFIER added to the non-clustered index pages. Since we did not add any duplicate values, the UNIQUIFIER column is set to zero.
Now, lets add some duplicate entries.
-- -- Let's insert some duplicate records -- INSERT INTO dbo.Uniquifier_Test (ID, Name) VALUES (1, 'aa') , (2, 'bb') GO
As you can see, where there are duplicate entries, the UNIQUIFIER column adds a unique value to distinguish between them. This incremental number is just within the same set of duplicate entries. Two duplicate rows for aa & 1 have the UNIQUIFIER value set to 0 and 1 respectively. For the next set of duplicates the incremental value starts over from 0. So it can accomodate a lot of duplicate entries.
So, the uniquifier column helps Sql Server distinguish between two identical entries in the non-clustered index.