Designing and Tuning the Indexes - Pro SQL Server Internals - page 124

SQL Server solves such problems by adding another nullable integer column called uniquifier to non-
unique clustered indexes. SQL Server populates uniquifiers with NULL for the first occurrence of the key value,
autoincrementing it for each subsequent duplicate inserted into the table.

■ the number of possible duplicates per clustered index key value is limited by integer domain values. You
cannot have more than 2,147,483,648 rows with the same clustered index key. this is a theoretical limit, and it is clearly
a bad idea to create indexes with such poor selectivity.

Note

Let's look at the overhead introduced by uniquifiers in non-unique clustered indexes. The code shown in
Listing 6-1 creates three different tables of the same structure and populates them with 65,536 rows each. Table dbo.
UniqueCI is the only table with a unique clustered index defined. Table dbo.NonUniqueCINoDups does not have any
duplicated key values. Finally, table dbo.NonUniqueCDups has a large number of duplicates in the index.

Listing 6-1. Nonunique clustered index: Table creation

create table dbo.UniqueCI
(
KeyValue int not null,
ID int not null,
Data char(986) null,
VarData varchar(32) not null
constraint DEF_UniqueCI_VarData
default 'Data'
);

create unique clustered index IDX_UniqueCI_KeyValue
on dbo.UniqueCI(KeyValue);

create table dbo.NonUniqueCINoDups
(
KeyValue int not null,
ID int not null,
Data char(986) null,
VarData varchar(32) not null
constraint DEF_NonUniqueCINoDups_VarData
default 'Data'
);

create /*unique*/ clustered index IDX_NonUniqueCINoDups_KeyValue
on dbo.NonUniqueCINoDups(KeyValue);

create table dbo.NonUniqueCIDups
(
KeyValue int not null,
ID int not null,
Data char(986) null,
VarData varchar(32) not null
constraint DEF_NonUniqueCIDups_VarData
default 'Data'
);