Clustered Index Rows with a Uniquifer.

All clustered indexes must be unique. The primary reason why a clustered Index must be unique is so that nonclustered index entries can point to exactly one specific row.

Consider the problem that would occur if a table were clustered by a non unique value of

last name. If a nonclustered index existed on a unique value, such as social security number,

And a query looked into the index for a specific social security number of 123-45-6789 and

found that its clustering key was ‘Smith,’ then if multiple rows with a last name of Smith

existed, the question would be—which one? How would the specific row with a social security

number of 123-45-6789 be located efficiently?

For a clustering key to be used effectively, all nonclustered index entries must refer to exactly

one row. Because that pointer is the clustering key in SQL Server, then the clustering key

must be unique.

If you build a clustered index without specifying the UNIQUE keyword, SQL Server guarantees

uniqueness internally by adding a hidden uniquifier column to the rows when necessary.

Note:

In SQL Server Books Online, the word uniquifier is written as unique identifier; however, the

internal tools—such as DBCC PAGE—spell it as we’ve spelled it here

This uniquifier is a 4-byte integer value added to the data row when the row’s clustering key is

not unique. Once added, it becomes part of the clustering key, meaning that it is duplicated

in every nonclustered index.

You can see whether or not a specifi c row has this extra value when you review the actual structure of index rows, as we will see later in this chapter.

As mentioned earlier,if your clustered index was not created with the UNIQUE property, SQL

Server adds a 4-byte integer to make each nonunique key value unique.

Because the clustering key is used to identify the base rows being referenced by nonclustered indexes (the bookmark lookup), there needs to be a unique way to refer to each row in a clustered index.

SQL Server adds the uniquifier only when necessary—that is, when duplicate keys are added

to the table.

As an example, we create a small table with all fi xed-length columns and then

add a clustered, nonunique index to the table:

USE AdventureWorks2008;

GO

CREATE TABLE Clustered_Dupes

(Col1 CHAR(5) NOT NULL,

Col2 INT NOT NULL,

Col3 CHAR(3) NULL,

Col4 CHAR(6) NOT NULL);

GO

CREATE CLUSTERED INDEX Cl_dupes_col1 ON Clustered_Dupes(col1);

If you look at the row in the sysindexes compatibility view for this table, you notice something