Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Example:

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
share|improve this question

1 Answer 1

up vote 4 down vote accepted

nope. You'll probably even get an error if you try to force your 'IS NULL' query to use your filtered index with an index hint.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.