Database Reference
In-Depth Information
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.NonUniqueCINoDups'), 2, null , 'DETAILED')
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.NonUniqueCIDups'), 2, null , 'DETAILED')
Figure 6-2.
Nonunique clustered index: Nonclustered indexes row size
There is no overhead in the nonclustered index in the
dbo.NonUniqueCINoDups
table. As you will recall, SQL
Server does not store offset information in a variable-length offset array for the trailing columns storing
NULL
data.
Nonetheless, the uniquifier introduces eight bytes of overhead in the
dbo.NonUniqueCIDups
table. Those eight bytes
consist of a four-byte uniquifier value, a two-byte variable-length data offset array entry, and a two-byte entry storing
the number of variable-length columns in the row.
We can summarize the storage overhead of the uniquifier in the following way. For the rows, which have a
uniquifier as
NULL
, there is a two-byte overhead if the index has at least one variable-length column that stores the
NOT
NULL
value. That overhead comes from the variable-length offset array entry for the uniquifier column. There is no
overhead otherwise.
In case the uniquifier is populated, the overhead is six bytes if there are variable-length columns that store
NOT
NULL
values. Otherwise, the overhead is eight bytes.
■
if you expect a large number of duplicates in the clustered index values, you can add an integer identity column
as the rightmost column to the index, thereby making it unique. this adds a four-byte predictable storage overhead to
every row as compared to an unpredictable
up to
eight-byte storage overhead introduced by uniquifiers. this can also
improve performance of individual lookup operations when you reference the row by all of the clustered index columns.
Tip
