title: Filtered index seek for `is NULL` condition and scan for opposite
body: <p>Assume there is a table like:</p>
  
  <pre><code>create table #data (ID int identity(1, 1) not NULL, Value int)
  </code></pre>
  
  <p>Put some data into it:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And two indexes:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Data is queried like:</p>
  
  <pre><code>select ID from #data where Value is NULL
  </code></pre>
  
  <p>or</p>
  
  <pre><code>select ID from #data where Value is not NULL
  </code></pre>
  
  <p>If I examine query plan, I see that in first case index seek is performed and in the second case index scan is performed. Why is it seek in first case and scan in second?</p>
  
  <p><strong>Addition</strong> after comments:</p>
  
  <p>If I create ordinary covering index instead of two filtered covering:</p>
  
  <pre><code>create index #ix_data on #data (Value) include (ID)
  </code></pre>
  
  <p>Query plan is showing index seek for both <code>is NULL</code> and <code>is not NULL</code> conditions, disregarding % of the NULL values in column (0% of NULLs or 10% or 90% or 100%, not matter).
  When there are two filtered indexes, query plan is showing index seek for <code>is NULL</code> always, and can be index scan or table scan (depending on % of the NULLs), but it is never index seek. So, seems, essentially the difference is in the way condition 'is not NULL' handled.</p>
  
  <p>It means, probably, that if index is intended for 'is not NULL' check only, then either normal index or filtered index should perform better and be preferred, isn't it? Which one?</p>
  
  <p>SqlServer 2008, 2008r2 and 2012</p>