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.
--Setup, actual queries after the space
--create table fltrind (a integer,b integer)
truncate table fltrind
DROP INDEX fltrind.nf
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

DROP INDEX fltrind.nf


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

Infact, increasing the rowcount so that >10% of the rows 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

1 Answer 1

up vote 5 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
Got it.. I presume this would be applicable in a scenario where the index is created on (a,xyz) WHERE a IS NOT NULL, if xyz contains 100's of values for each a, SQL would still be able to filter out the a's where it a IS NULL trivially during the query due to the tree structure of the index? –  Akash Aug 15 '14 at 17:35
@akash yes, should be equivalent. –  Aaron Bertrand Aug 15 '14 at 17:36

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.