O SQL Server 2008 introduziu o conceito de Filtered Index, que nos permite criar um índice na porção dos dados necessários. Conseqüentemente, reduzindo o espaço utilizado em disco, o tempo de manutenção e até mesmo o de pesquisa na árvore de índices.

Essa é uma das features que tem contribuído muito nos projetos em SQL Server 2008 nos quesitos otimização de queries, índices e diminuição do espaço alocado.

De forma simplificada, um índice é considerado um “Filtered Index” quando possuir a cláusula “where” na sua criação. Esse tipo de filtro pode ser apenas aplicado aos índices do tipo non-clustered e geralmente a sintaxe é a descrita abaixo:

1: CREATE NONCLUSTERED INDEX [name]
2: ON [tablename] ([columnlist])
3: Include ([columnlist])
4: WHERE [filteredcriteria];

Exemplificando através de um cenário real, no qual já foi implementada essa solução, encontramos o seguinte exemplo:

Em uma grande indústria, existe a tabela de contatos que são categorizados pela coluna chamada “nmcategoria”.

Para ilustrar esse cenário, recriei a tabela em questão e fiz uma carga de dados utilizando o SQL Data Generator com um milhão de registros. Abaixo segue a imagem da tabela.

A maior parte das consultas é baseada em uma única expressão:

1: select cdcontato, nmcontato, nmcategoria
2: from contato
3: where nmcategoria = 'meat'

A consulta acima retornará (neste cenário) mais de 90 mil registros, primeiro fator para utilizarmos os Filtered Indexs é que economizamos espaço em disco, pois o índice só terá referência aos registros que estão na cláusula da sua criação.

Abaixo vejamos o plano de execução da consulta padrão.

Se criarmos agora os índices e executarmos os testes não haverá muito retorno, devido ao fato de que a nossa tabela não possui fragmentações, alterações e modificações.

Para seguir o exemplo, vou criar uma nova coluna a essa tabela, com um valor default e executar dois scripts de alteração na tabela. Veja abaixo:

1: alter table contato add nrvalor int default 0
2: go
3: update contato set nrvalor = 2 where nmcategoria = 'meat'
4: go
5: update contato set nrvalor = 5 where nmcategoria = 'Produce'

Após essas alterações, podemos verificar uma alteração no plano de execução da consulta padrão (vide gráfico abaixo).

Seguindo as recomendações do Execution plan, podemos criar um índice para o campo chave “nmcategoria” e incluir os campos: cdcontato, nmcontato e nrvalor.

1: CREATE NONCLUSTERED INDEX ix_contato01
2: ON [dbo].[contato] ([nmcategoria])
3: INCLUDE ([cdcontato],[nmcontato],[nrvalor])

Com esse índice criado, podemos analisar novamente o plano de execução e verificar que não houve mudança. Como isso é um exemplo, propositalmente o mesmo foi criado com poucas informações para que seja fácil de assimilar o objetivo.

No entanto, se criarmos um índice com um filtro pelo campo de nome da categoria teremos um retorno significativo. Abaixo vou colocar o script para criar esse índice e o Execution Plan.

1: CREATE NONCLUSTERED INDEX ix_contato02
2: ON [dbo].[contato] ([nmcategoria])
3: INCLUDE ([cdcontato],[nmcontato],[nrvalor])
4: WHERE nmcategoria = 'Meat'

A seguir, faremos três considerações importantes sobre o assunto discutido.

01. Para validar o nome dos índices e a quantidade de linhas que ele retorna, pode-se utilizar o t-SQL a seguir:

1: SELECT
2:   ix.name
3:  ,par.rows
4:  ,ix.filter_definition
5: FROM
6:   sys.partitions par
7:   INNER JOIN sys.indexes ix
8:     ON par.object_id = ix.object_id
9:        AND par.index_id = ix.index_id
10: WHERE
11:   OBJECT_NAME(ix.object_id) = 'contato'
12:   AND ix.name like '%ix%'

O resultado dessa consulta no ambiente foi o exibido abaixo:

02. É importante perceber que, nos exemplos acima, ambos os índices foram mantidos e no último plano de execução o próprio engine de consultas do SQL Server se optou por utilizar o ix_contato02, ao invés, do ix_contato01. Ou seja, não foi preciso utilizar um Hint na consulta para forçar o plano de execução.

03. Avaliar a utilização desse recurso envolve uma análise minuciosa das variáveis envolvidas, nem sempre esse tipo de recurso poderá ser considerado uma solução.

Concluindo, essa nova feature atende alguns cenários específicos, onde temos um padrão de consulta determinado por um único grupo de registros. Minha recomendação para criação de novos índices é sempre analisar minuciosamente o padrão e o comportamento das consultas dos usuários.