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 ...