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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
--Setup, actual queries after the space
--create table fltrind (a integer,b integer)
truncate table fltrind
DROP INDEX fltrind.f
DECLARE @i integer
SET @i=0
while @i<100
INSERT INTO fltrind(a) values(@i)
SET @i=@i+1
INSERT INTO fltrind(a,b) values(9000,0900)

insert into fltrind(a,b)
select top 100000 f1.a,f1.b from fltrind f1 , fltrind f2

create nonclustered index nf on fltrind(b) INCLUDE(a)
create nonclustered index f on fltrind(b) INCLUDE(a) where b is not null


select a from fltrind where b is not null



select a from fltrind where b is not null

Looking at the query plan, the filtered index is not used as long as the non-filtered index is present. Any idea why, and how can I get it to use the filtered index?

Dropping the non filtered index nf makes the optimizer use the filtered index f.

In fact, increasing the rowcount so that >10% of the rows qualify results in a table scan when the non filtered index is dropped.

share|improve this question
@AaronBertrand I went through the 1st link just now, but the situation described there is virtually the same as mine, (except that IS NULL is used in the blog, but IS NOT NULL is used in my query. However, thats the situation for both the index and query) The optimizer did use the filtered index in the blog. In my case it does not (except when the filtered index is the only one) – Akash Aug 15 '14 at 17:18
up vote 6 down vote accepted

Since SQL Server can skip NULL rows to start the range scan, the cost of either index is identical, so this is basically a coin toss for the optimizer. Look at the plans in SQL Sentry Plan Explorer* by default and when you hint the index (click to enlarge):

enter image description here

enter image description here

Since it's a toss-up, I don't know what benefit you'd get out of forcing SQL Server to choose one of two equally valid options.

* Disclaimer: I work for SQL Sentry.

share|improve this answer

Your Answer


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.