SQL Server 2008 introduced a lot of new features one of which is filtered indexes. Filtered indexes can be used as an alternative to indexed views and partitioning to improve data retrieval performance on large tables.
A filtered index is a non-clustered index with a where clause. The index will only contain the rows which satisfy the where clause. This means the index will require less storage space and have better performance.
To create the filtered index the syntax is relatively simple:
CREATE NONCLUSTERED INDEX {INDEX_NAME}
ON {TABLE}({FIELD})
WHERE {FIELD} — Insert Where criteria here.
GO
The query optimiser should pick the best index to use however you can use a hint to force the query optimiser to use the filtered index:
SELECT * FROM {TABLE} WITH(INDEX({INDEX_NAME}))
WHERE {FIELD}
While they give a performance boost there are two main limitations with using a filtered index. Firstly they are only created on a single table. Second, complex criteria like the LIKE clause are not allowed.