The filtered index looks for the `is NULL` condition and scans the opposite.

Question

Suppose there is a table like:

create table #data (ID int identity(1, 1) not NULL, Value int) 

Put some data in it:

 insert into #data (Value) select top (1000000) case when (row_number() over (order by @@spid)) % 5 in (0, 1) then 1 else NULL end from sys.all_columns c1, sys.all_columns c2 

And two indexes:

 create index #ix_data_n on #data (Value) include (ID) where Value is NULL create index #ix_data_nn on #data (Value) include (ID) where Value is not NULL 

Data is requested as:

 select ID from #data where Value is NULL 

or

 select ID from #data where Value is not NULL 

If I examine the query plan, I see that in the first case, the index is searched, and in the second, the index scan. Why is he searching in the first case and scanning the second?

Adding after comments:

If I create a regular coverage index instead of two filtered coatings:

 create index #ix_data on #data (Value) include (ID) 

The query plan displays an index search for the conditions is NULL and is not NULL , not counting the% NULL values ​​in the column (0% of NULL or 10% or 90% or 100%, it does not matter). When there are two filtered indexes, the query plan shows index search for is NULL always and there may be an index scan or a table scan (depending on% of NULL), but it is never an index search. So, there seems to be essentially a difference in how the non-NULL condition is handled.

This means, probably, that if the index is intended only to check "is not NULL", then the normal index or filtered index should work better and be preferred, right? Which one?

SqlServer 2008, 2008r2 and 2012

Answer 1

Searching and scanning query plans that you see is a red herring.

In both cases, the query is answered by scanning the corresponding nonclustered index from beginning to end, returning each row.

After examining the XML query plan, you will see that the Seek index predicate is "# data.Value = Scalar Operator (Null)", which is pointless because each row matches these criteria.