Chapter 4 SQLServer statistical information (4) filtering index statistics

A new feature to enhance non-clustered indexes, filterindex, was introduced from 2008. You can use statements with where conditions to create non-clustered indexes, filters out unnecessary data, reduces index maintenance overhead and storage space, and improves query performance. Preparations: At AdventureWorks201

A new function to enhance non-clustered indexes, filter index, was introduced from 2008. A statement with the where condition can be used to create a non-clustered index, filters out unnecessary data, reduces index maintenance overhead and storage space, and improves query performance. Preparations: At AdventureWorks201

Preface:

From 2008, an enhanced non-aggregation is introduced.IndexNew Features --FilterIndex(Filter index), you can use statements with the where condition to create non-ClusteringIndex,FilterDrop unnecessary data and reduceIndexTo improve query performance.

Preparations:

On AdventureWorks2012, there is a Production. WorkOrder table, which will be used for demonstration.

Steps:

1. Create a non-aggregationIndexIn the Production. WorkOrder column:

CREATE INDEX idx_WorkOrder_ScrapReasonID ON Production.WorkOrder(ScrapReasonID)GO

2. Create a non-aggregationIndexThe correspondingStatisticsObject:

DBCC SHOW_STATISTICS('Production.WorkOrder',IX_WorkOrder_ScrapReasonID)GO

3. The result is as follows:

4. Check the All denisty column and delete the previousIndexAnd createFilterIndex:

DROP INDEX IX_WorkOrder_ScrapReasonID ON Production.WorkOrderGO CREATE INDEX IX_WorkOrder_ScrapReasonID ON Production.WorkOrder(ScrapReasonID)WHERE ScrapReasonID IS NOT NULL GO

5. Run the command again and check the All density column, and deleteIndex:

 DBCC SHOW_STATISTICS('Production.WorkOrder',IX_WorkOrder_ScrapReasonID)GO DROP INDEX IX_WorkOrder_ScrapReasonID ON Production.WorkOrderGO 

6. The result is as follows:

Analysis:

From the comparison results in the above two figures, we can know that the first result set is different in the row sampled column. The first result set is 72591, and the second result is 729, with a difference of nearly 100 times, in the Filter Expression column, one is null and the other is a value.

In the All density column, the first one is the ScriptReasonID column, which has no obvious difference. But there is a big difference in the second one. Because ScriptReasonID is directed to aggregationIndexNon-clusteringIndexColumn.