SQL Server 2005 Index Filter Feature

Question

I was told that there is new a feature in SQL Server 2005 called index filters.

What I want to do is add an Index to a column and have the index ignore null values.

I can't find good information on this feature (maybe my source is wrong). Can anyone provide additional information on this feature?

Answer 1

I think you are speaking about filtered indexes, which were introduced in SQL Server 2008, not 2005.

For information, have a look at this article: http://www.sql-server-performance.com/articles/dba/Filtered%5FIndexes%5Fin%5FSQL%5FServer%5F2008%5Fp1.aspx

Or just do a google search for "sql server filtered indexes".

Answer 2

CREATE INDEX ix_mytable_mycolumn ON mytable(mycolumn) WHERE mycolumn IS NOT NULL

This will work only in SQL Server 2008, though.

From the docs:

WHERE <filter_predicate>

Creates a filtered index by specifying which rows to include in the index. The filtered index must be a nonclustered index on a table. Creates filtered statistics for the data rows in the filtered index.