Discover the Benefits of Filtered Indexes and Statistics

Add to the query a filter based on the index key column—OrderDate in our case—as the following query shows (call it Query 4):

SELECT SalesOrderID, CustomerID, OrderDate,
 TotalDue
FROM Sales.SalesOrderHeader
WHERE TerritoryID = 5
 AND OrderDate >= ‘20040101’; 

Figure 4 shows the execution plan for this query. The plan shows that the optimizer efficiently uses a seek operation (plus a partial scan) against the index idx_ territory5_orderdat e, because the index not only covers the query but also sorts the data by OrderDate. Only the relevant range of rows is scanned within the index since OrderDate is the first (and only) key in the index.

Similar to filtered indexes, SQL Server 2008 also lets you create filtered statistics. For example, the following code creates filtered statistics on the OrderDate column in the Sales.SalesOrderHeader table, only for the subset of rows from the table where the territory ID is 4:

CREATE STATISTICS stats_territory4_orderdate ON
 Sales.SalesOrderHeader(OrderDate) WHERE TerritoryID = 4;

Filtered indexes can also be used in conjunction with another new SQL Server 2008 feature—sparse columns, which don’t consume any storage space for NULLs. Sparse columns is a topic for another article, but for now I’ll just say that in SQL Server 2008 you will be able to create a large number of sparse columns (as many as 30,000) and support those columns with a large number of indexes/statistics (as many as 1,000).

Unique Constraint with Multiple NULLs
You can define a filtered index as unique if you need to. Doing so can help the optimizer and can be used to enforce uniqueness. For example, DBAs sometimes need to enforce a unique constraint that allows multiple NULLs (i.e., prevents duplicates in known values, but not with NULLs). Although SQL Server doesn’t support such a constraint, filtered indexes offer an easy alternative. The following code shows an example:

IF OBJECT_ID(‘dbo.T1’, ‘U’) IS NOT NULL DROP TABLE dbo.T1;

CREATE TABLE dbo.T1(col1 INT NULL, col2 VARCHAR(10) NOT NULL);

CREATE UNIQUE NONCLUSTERED INDEX idx_col1_
  notnull
 ON dbo.T1(col1)
 WHERE col1 IS NOT NULL;

As you can see, a unique index is created on T1.col1, with a filter that excludes NULLs. This means that uniqueness will be enforced on known values but not on NULLs because NULLs aren’t part of the index. To test the index, try to insert two rows with the same known col1 value:

INSERT INTO dbo.T1(col1, col2) VALUES(1, ‘a’);
INSERT INTO dbo.T1(col1, col2) VALUES(1, ‘a’);

The first INSERT will be successful, but the second will fail with the error message in Figure 5. Conversely, you will have no problem inserting multiple rows with NULL in col1:

INSERT INTO dbo.T1(col1, col2) VALUES(NULL, 'a');
INSERT INTO dbo.T1(col1, col2) VALUES(NULL, 'a');

When you’re done experimenting with filtered indexes, run the following code for cleanup:

USE AdventureWorks;
DROP INDEX Sales.SalesOrderHeader.idx_currate_
   notnull;
DROP INDEX Sales.SalesOrderHeader.idx_
   freight_5000_or_more;
DROP INDEX Sales.SalesOrderHeader.idx_
   territory5_orderdate;
DROP STATISTICS Sales.SalesOrderHeader.stats_
  territory4_orderdate;DROP TABLE dbo.T1;

Benefits
Filtered indexes and statistics let you optimize your queries for certain subsets of rows but don’t incur the same costs as regular indexes. Filtered indexes consume less storage space, are faster and cheaper to maintain, and can be used as an alternative to a unique constraint that allows multiple NULLs.