Search code examples
sql-serverindexingindexed-view

SQL Server 2005 Index Filter Feature

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?

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