O'Reilly logo

Professional Microsoft® SQL Server® 2008 Administration by Steven Wort, Ross LoForte, Wayne Snyder, Ketan Patel, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

15.4. Filtered Indexes and Filtered Statistics

As mentioned previously, filtered indexes and filtered statistics are new features in SQL Server 2008. Filtered indexes or stats can take a WHERE predicate to indicate which rows are to be indexed. Obviously, you are indexing only a portion of rows in a table, which means you can only create a nonclustered filtered index. If you try to create a filtered clustered index, SQL Server will return a syntax error. Here is the syntax to create the filtered index:

This is not the complete CREATE INDEX syntax, but rather a portion to indicate the WHERE predicate required to create the filtered index. Refer to the topic "CREATE INDEX (Transact-SQL)" in BOL for the complete syntax.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]

Why do you need a nonclustered index with subset of data in a table? A well-designed filtered index can offer the following advantages over full-table indexes:

  • Improved query performance and plan quality — If the index is deep (more pages because of more data), traversing an index takes more I/O and results in slow query performance. If you have a very large table and you know that there are more user queries on a well-defined subset of data, then creating a filtered index will make queries run faster, as less I/O will be performed because the number of pages is less for the smaller amount ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required