Database Reference
In-Depth Information
Figure 6-1.
Nonunique clustered index: Clustered indexes row size
Even though there is no duplicated key values in the
dbo.NonUniqueCINoDups
table, there are still two extra bytes
added to the row. SQL Server stores a uniquifier in variable-length sections of the data, and those two bytes are added
by another entry in a variable-length data offset array.
In this case, when a clustered index has duplicate values, uniquifiers add another four bytes, which makes for an
overhead of six bytes total.
It is worth mentioning that in some edge cases, the extra storage space used by the uniquifier can reduce the
number of the rows that can fit into the data page. Our example demonstrates such a condition. As you can see,
dbo.
UniqueCI
uses about 15 percent fewer data pages than the other two tables.
Now let's see how the uniquifier affects nonclustered indexes. The code shown in Listing 6-3 creates nonclustered
indexes in all three tables. Figure
6-2
shows physical statistics for those indexes.
Listing 6-3.
Nonunique clustered index: Checking nonclustered indexes row size
create nonclustered index IDX_UniqueCI_ID
on dbo.UniqueCI(ID);
create nonclustered index IDX_NonUniqueCINoDups_ID
on dbo.NonUniqueCINoDups(ID);
create nonclustered index IDX_NonUniqueCIDups_ID
on dbo.NonUniqueCIDups(ID);
select index_level, page_count, min_record_size_in_bytes as [min row size]
,max_record_size_in_bytes as [max row size]
,avg_record_size_in_bytes as [avg row size]
from sys.dm_db_index_physical_stats
(db_id(), object_id(N'dbo.UniqueCI'), 2, null , 'DETAILED')
