--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
Observando o plano de consulta, o índice filtrado não é usado enquanto o índice não filtrado estiver presente. Alguma ideia do porquê e como posso fazer com que ele use o índice filtrado?
A eliminação do índice não filtrado nf
faz com que o otimizador use o índice filtrado f
.
Na verdade, aumentar a contagem de linhas para que >10% das linhas sejam qualificadas resulta em uma varredura de tabela quando o índice não filtrado é descartado.
Como o SQL Server pode pular linhas NULL para iniciar a varredura de intervalo, o custo de qualquer um dos índices é idêntico, portanto, isso é basicamente um sorteio para o otimizador. Veja os planos no SQL Sentry Plan Explorer * por padrão e quando der a dica do índice ( clique para ampliar ):
Como é uma disputa, não sei que benefício você obteria ao forçar o SQL Server a escolher uma das duas opções igualmente válidas.
*
Isenção de responsabilidade: eu trabalho para o SQL Sentry.