Statistics, Data Distribution, and Cardinality - SQL Server Query Performance Tuning - page 212

Figure 12-23 shows the header and the density of the output from DBCC SHOWSTATISTICS run against this
new index.
DBCC SHOW_STATISTICS('Sales.SalesOrderHeader',IX_Test);
Figure 12-23. Statistics header of an unfiltered index
If the same index is re-created to deal with values of the column that are not null , it would look something
like this:
CREATE INDEX IX_Test
ON Sales.SalesOrderHeader (PurchaseOrderNumber)
WHERE PurchaseOrderNumber IS NOT NULL
WITH DROP_EXISTING = ON;
And now, in Figure 12-24 , take a look at the statistics information.
Figure 12-24. Statistics header for a filtered index
First you can see that the number of rows that compose the statistics has radically dropped in the filtered index
because there is a filter in place, from 31465 to 3806. Notice also that the average key length has increased since you're
no longer dealing with zero-length strings. A filter expression has been defined rather than the NULL value visible in
Figure 12-21 . But the unfiltered rows of both sets of data are the same.