Isso só me deixa louco.
Considere uma tabela simples com colunas irrelevantes removidas:
create table boxes (
row_id int not null identity(1,1) primary key,
location varchar(15) null,
dismantled bit not null default 0,
rank int not null default 0
/* irrelevant columns, text and numbers */
)
Para uma determinada consulta importante, quero um índice filtrado:
create nonclustered index anindex ON boxes (rank)
where (dismantled=0 and location is null)
Isso cria um índice que contém cerca de 150 registros de cerca de 150 mil. O que é bom.
Agora consultamos a tabela:
select top (1) row_id
from boxes
where dismantled = 0 and location is null
order by rank;
O plano de execução é estranho. Varredura de índice anindex
acompanhada por uma pesquisa de marcador usando o procurado row_id
para confirmar que location
é null
. Os registros que passam na pesquisa são selecionados.
Por que na Terra? location
é null
pela própria definição do índice utilizado, não é?
Agora, se eu fizer uma besteira e incluir location
no índice filtrado...
create nonclustered index anindex ON boxes (location, rank)
where (dismantled=0 and location is null)
Então, de repente, o plano de execução é ótimo, o índice continua anindex
sem pesquisas.
Este é apenas um exemplo, me deparo com esse problema toda vez que quero usar um índice filtrado. Toda vez acabo sendo forçado a incluir os campos inúteis, sobre os quais o índice é filtrado, na lista de campos indexados, e só assim o servidor usa o índice corretamente; caso contrário, ele o verifica ou o ignora completamente.
O que da? É uma prática recomendada incluir os campos inúteis?
Isso foi levantado anteriormente em Connect e Closed como "Won't Fix" .
Isso é bastante decepcionante IMO. Talvez vote positivo e comente esse item.
Edit: BOL tem um exemplo de uso
WHERE EndDate IS NOT NULL
em um índice filtrado e isso não faz uma pesquisa de chave, mas a substituiçãoIS NOT NULL
porIS NULL
leva a uma pesquisa, então parece que às vezes é evitado ...