Statistics in SQL Server - Microsoft SQL Server 2012 Performance Tuning - page 295

6. Now, look at the following screenshot of statistics, generated after creating a

filtered index:

How it works...

Now, since we know that DBCC SHOW_STATISTICS returns three result sets. If you observe
the first result sets, you get to know the difference in the Row Sampled column. In the first
screenshot, it is 72591, and the recent screenshot has only 729, which is big difference. In
the Filter Expression column, the filter we have used will be displayed rather than NULL .

In the All density column, the first row represents the ScriptReasonID column. There is no
major difference between the two screenshots but the second row shows a major difference,
because the ScriptReasonID column is a non-clustered index that points to the clustered
index of the table WorkOrderID , and many WorkOrderID , are eliminated due to the filter we
have applied.