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%.
No comments:
Post a Comment