Understanding and Examining the Uniquifier in SQL Server

By: Ken Simmons   |   Comments (2)   |   Related: > Indexing

Problem

When you create a non unique clustered index, SQL Server creates a hidden 4 byte uniquifier column that ensures that all rows in the index are distinctly identifiable. However, SQL Server will only use the uniquifier when necessary. What exactly does this mean? Does a new uniquifier column get added when you insert the first non unique row? Since the uniquifier is only four bytes, does that limit my table to 2,147,483,647 rows?

Solution

The easiest way to answer these questions is to run some tests.

First let's take a look at the behavior with a Unique Clustered Index.

CREATE TABLE UniquifierTest1 (Col1 INT, Col2 INT)

CREATE UNIQUE CLUSTERED INDEX idxClustered ON UniquifierTest1 (Col1) 
CREATE NONCLUSTERED INDEX idxNonClustered ON UniquifierTest1 (Col2) 

INSERT INTO UniquifierTest1 VALUES (1,1) t1 VALUES (2,2) 
INSERT INTO UniquifierTest1 VALUES (3,3)

Now, if you look at the Non Clustered Index, that was just created on Col2, you will see that it includes Col2 as well as Col1. The Non Clustered Index needs to include Col1, so it can reference the exact row that it correlates to in the Clustered Index. We can find the page number of the Non Clustered Index and display the data using the following commands. Note that 7629 used in the DBCC PAGE command is the page number I retrieved using the DBCC IND command.

--Get the Page Number of the Non Clustered Index
DBCC IND (AdventureWorks, UniquifierTest1, -1)

--Examine the Results of the Page
DBCC TRACEON (3604); 
DDBCC PAGE(AdventureWorks, 1, 7629, 3); 

You can see the output of the Non Clustered Index in the image below.

Now, let's run the same test except this time I will not force the Clustered Index to be unique.

CREATE TABLE UniquifierTest2 (Col1 INT, Col2 INT) 

CREATE CLUSTERED INDEX idxClustered ON UniquifierTest2 (Col1) 
CREATE NONCLUSTERED INDEX idxNonClustered ON UniquifierTest2 (Col2) 

INSERT INTO UniquifierTest2 VALUES (1,1) 
INSERT INTO UniquifierTest2 VALUES (2,2) 
INSERT INTO UniquifierTest2 VALUES (3,3) 

--Get the Page Number of the Non Clustered Index 
DBCC IND (AdventureWorks, UniquifierTest2, -1) 

--Examine the Results of the Page 
DBCC TRACEON (3604); 
DBCC PAGE(AdventureWorks, 1, 22437, 3); 

You can see by the following results that the Non Clustered Index now includes a UNIQUIFIER column. Also notice that we have yet to insert duplicate data, so all of the values are displayed as 0. Even though the values are displayed as 0, they are actually NULL so they do not require space. The conversion to 0 is performed in the DBCC PAGE command for readability.

Now, lets see what happens when some non unique values are added.

INSERT INTO UniquifierTest2 VALUES (1,1) 
INSERT INTO UniquifierTest2 VALUES (1,1) 

DBCC PAGE(AdventureWorks, 1, 22437, 3); 

You can see by the output below that only the duplicate items that needed to be distinctly identified were changed. In other words, the Uniquifer does not act like an incrementing identity column across the entire table.

What this means is that you are limited to positive integer values only for the number of duplicates you can have. If you try to insert over 2,147,483,647 duplicate values you will receive the following error message.

Next Steps

• Review the DBCC IND and DBCC PAGE commands
• Check out some other tips on Indexing

About the author

Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips