3
--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
BEGIN
INSERT INTO fltrind(a) values(@i)
SET @i=@i+1
END
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

UPDATE STATISTICS fltrind WITH FULLSCAN

select a from fltrind where b is not null

DROP INDEX fltrind.nf

UPDATE STATISTICS fltrind WITH FULLSCAN

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.

6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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