Sql Server Performance: Filtered Index a beauty of Sql Server 2008

Filtered index is one of the beauties of Sql Server 2008 which is very helpful to increase query performance with less index storage. A non clustered index with WHERE clause, at the time of creation, is called a Filtered index. Filtered index basis on well defined subset of data from very large tables. Let’s see filtered index in action.

SET STATISTICS IO ON

USE [AdventureWorks]

GO

SELECT  SalesOrderID,

        CarrierTrackingNumber,

        OrderQty,

        ProductID,

        UnitPrice

FROM    Sales.SalesOrderDetail

WHERE   CarrierTrackingNumber LIKE '%98'

Table 'SalesOrderDetail'. Scan count 1, logical reads 1238, physical reads 0

 

To avoid clustered index scan, lets create a covering non-clustered index. Covering index is used to avoid bookmarks lockups.

CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_CarrierTrackingNumber]

ON [Sales].[SalesOrderDetail] ( [CarrierTrackingNumber] ASC )

    INCLUDE ( SalesOrderID, OrderQty, ProductID, UnitPrice )

WITH (DROP_EXISTING = ON)

ON  [PRIMARY]

GO

SELECT  SalesOrderID,

        CarrierTrackingNumber,

        OrderQty,

        ProductID,

        UnitPrice

FROM    Sales.SalesOrderDetail

WHERE   CarrierTrackingNumber LIKE '%98'

Table 'SalesOrderDetail'. Scan count 1, logical reads 639, physical reads 0

Logical reads dropped from 1238 to 639 but still we can increase query efficiency by converting our covering non-clustered index to a filtered index by including WHERE clause when creating index. In our example we just want to add rows in index where CarrierTrackingNumber is not null. Keyword DROP_EXISTING is used to re-create existing index with single atomic step.

CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_CarrierTrackingNumber]

ON [Sales].[SalesOrderDetail] ( [CarrierTrackingNumber] ASC )

    INCLUDE ( SalesOrderID, OrderQty, ProductID, UnitPrice )

WHERE [CarrierTrackingNumber] IS NOT NULL -- Place a filter

WITH (DROP_EXISTING = ON)

ON  [PRIMARY]

Table 'SalesOrderDetail'. Scan count 1, logical reads 429, physical reads 0

Results shows an improvement in logical reads up to 33%.