Database Reference
In-Depth Information
Filtered Indexes
A filtered index is a nonclustered index that uses a filter, basically a
WHERE
clause, to create a highly selective set of
keys against a column or columns that may not have good selectivity otherwise. For example, a column with a large
number of
NULL
values may be stored as a sparse column to reduce the overhead of those
NULL
values. Adding a
filtered index to the column will allow you to have an index available on the data that is not
NULL
. The best way to
understand this is to see it in action.
The
Sales.SalesOrderHeader
table has more than 30,000 rows. Of those rows, 27,000+ have a
null
value in the
PurchaseOrderNumber
column and the
SalesPersonId
column. If you wanted to get a simple list of purchase order
numbers, the query might look like this:
SELECT soh.PurchaseOrderNumber,
soh.OrderDate,
soh.ShipDate,
soh.SalesPersonID
FROM Sales.SalesOrderHeader AS soh
WHERE PurchaseOrderNumber LIKE 'PO5%'
AND soh.SalesPersonID IS NOT NULL;
;
Running the query results in, as you might expect, a clustered index scan, and the following I/O and execution
time, as shown in Figure
9-8
:
Table 'SalesOrderHeader'. Scan count 1, logical reads 689
CPU time = 0 ms, elapsed time = 87 ms.
Figure 9-8.
Execution plan without an index
To fix this, it is possible to create an index and include some of the columns from the query to make this a
covering index (as shown in Figure
9-9
).
CREATE NONCLUSTERED INDEX IX_Test
ON Sales.SalesOrderHeader(PurchaseOrderNumber,SalesPersonID)
INCLUDE (OrderDate,ShipDate);
Figure 9-9.
Execution plan with a covering index
