12th Chapter--sqlserver Statistics (4)--Statistics on filtered indexes

Original: 12th Chapter--sqlserver Statistics (4)--statistical information on the filtered index

Objective:

From The beginning, a new feature is introduced to enhance the nonclustered index-filter index, youcan use a statement with a where condition to create a nonclustered index, filter out the unwanted data, Improve query performance by reducing maintenance overhead and storage space for indexes.

Preparatory work:

On the AdventureWorks2012 , there is a production.workorder table that will be used to illustrate the table.

Steps:

1. Create a nonclustered index on the Production . WorkOrder columns:

CREATE INDEX Idx_workorder_scrapreasonid on Production.WorkOrder (ScrapReasonID) GO

2. creating a nonclustered index automatically updates the corresponding statistic object:

DBCC show_statistics (' Production.WorkOrder ', Ix_workorder_scrapreasonid) GO

3. The results are as follows:

4. to view the all denisty column, now delete the previous index and create a filtered index:

DROP INDEX Ix_workorder_scrapreasonid on Production.workordergo CREATE INDEX Ix_workorder_scrapreasonid on Production.WorkOrder (ScrapReasonID) WHERE ScrapReasonID is not NULL GO

5. Execute and view the all density column again, and finally delete the index:

6. The results are as follows:

Analysis:

From the above two figures in the comparison of the results can be known, the first result set, the row sampled column is different, the first is 72591, the second is 729, the difference is nearly three times, in The Filter Expression column, one that is null , and one that has a value.

In the all density column, the first one shows the Scriptreasonid column, which has no obvious difference. But the second one is quite different. Because Scriptreasonid is a nonclustered index column that points to a clustered index.

12th Chapter--sqlserver Statistics (4)--Statistics on filtered indexes