Does having a filtered index on a column improve queries with a WHERE clause that is the opposite of the filter condition?
Since the filtered index specifies what not to return, does it reduce the number of records to search?
CREATE TABLE MyTable ( ColumnA INT PRIMARY KEY CLUSTERED, ColumnB INT NULL ) CREATE NONCLUSTERED INDEX IX_MyTable_Filtered_ColumnB ON MyTable (ColumnB) WHERE ColumnB IS NOT NULL
The index should improve this query:
SELECT ColumnA FROM MyTable WHERE ColumnB IS NOT NULL
Will it make any difference to this one?
SELECT ColumnA FROM MyTable WHERE ColumnB IS NULL