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?

score 9 · Accepted Answer · edited Jun 20 '20 at 09:12

9

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 13 '09 at 13:49

Quassnoi

413,100
91
616
614

2

Filtered index is only available in 2008. – deutschZuid Aug 08 '11 at 00:40
4

@James: and which year did I say? – Quassnoi Aug 08 '11 at 05:58
Sorry. Might not have read your answer properly. My apologies; – deutschZuid Aug 08 '11 at 21:38

score 7 · Answer 2 · answered Oct 13 '09 at 13:44

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

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

SQL Server 2005 Index Filter Feature

2 Answers2