Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Understanding and Examining the Uniquifier in SQL Server


By:   |   Read Comments (2)   |   Related Tips: More > Indexing


SQL Server Conference Giveaway - click to learn more


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.

You can see the output of the Non Clustered Index

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.

You can see by the following results that the Non Clustered Index now includes a UNIQUIFIER column

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.

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.

 you are limited to positive integer values only for the number of duplicates you can have

Next Steps


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, April 12, 2016 - 6:19:38 PM - Greg Coopman Back To Top

 Great Article.  Love the clear examples, especially with DBCC PAGE.

What I can take from your example, is that if you added an additional identity column in the second position of to the clustered index, then the uniquifier should disappear and be replaced with the new identity column (also 4 bytes) and the size of both the clustered and nonclustered indexes would remain the same?

If this is true, by adding an identity column to the table (which lets say does not have one), yes would cause an increase in the table size (4 bytes per row), but not cause any difference in the sizes of the indexes already in existence.  This new identity could then be useful in some maintenance situations where one wants to update / delete specific rows  (such as duplicates) where I find using a explicit unique column value like an identity comes in very handy. 

 

 

 

 


Friday, March 18, 2016 - 10:13:43 AM - Channdeep Singh Back To Top

Very good tip....cleared few doubts. Thanks a lot sir.


Learn more about SQL Server tools