Microsoft® SQL Server 2012 Unleashed

Index Densities

SQL Server stores the density values of each column in the index for use in queries where the SARG value is not known until runtime or when the SARG is on multiple columns of the index. For composite keys, SQL Server stores the density for the first column of the composite key; for the first and second columns; for the first, second, and third columns; and so on. This information is shown in the All density section of the DBCC SHOW_STATISTICS output in Listings 34.4 and 34.5.

Index density essentially represents the inverse of all unique key values of the key. The density of each key is calculated by using the following formula:

Key density = 1.00 / Count of distinct key values in the table

Therefore, the density for the LastName ...