By: Ken Simmons | Updated: 2010-08-03 | Comments (2) | Related: More > Indexing
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?
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.
Last Updated: 2010-08-03
About the author
View all my tips