Criar índices filtrados

Artigo
09/04/2023

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Este artigo descreve como criar um índice filtrado usando o SQL Server Management Studio (SSMS) ou Transact-SQL. Um índice filtrado é um índice rowstore não clusterizado otimizado baseado em disco, criado especialmente para consultas que fazem seleções de um subconjunto bem definido de dados. Ele usa um predicado de filtro para indexar uma parte das linhas da tabela. Um índice filtrado bem projetado pode aprimorar o desempenho da consulta, bem como reduzir os custos de manutenção e de armazenamento do índice em comparação com os índices de tabela completa.

Os índices filtrados podem oferecer as seguintes vantagens com relação aos índices de tabela completa:

O desempenho de consultas e a qualidade do plano foram aprimorados.

Um índice filtrado bem projetado melhora o desempenho das consultas e a qualidade do plano de execução porque é menor do que um índice não clusterizado de tabela completa e possui estatísticas filtradas. As estatísticas filtradas são mais precisas do que as estatísticas de tabela completa, pois abrangem apenas as linhas do índice filtrado.
Redução dos custos de manutenção do índice.

A manutenção do índice é feita apenas quando as instruções DML (linguagem de manipulação de dados) afetam os dados do índice. Um índice filtrado reduz os custos de manutenção em comparação com o índice não clusterizado de tabela completa porque é menor e a manutenção é feita somente quando seus dados são alterados. É possível ter um grande número de índices filtrados, especialmente quando eles contêm dados que são raramente alterados. Do mesmo modo, se um índice filtrado tiver apenas dados modificados com frequência, seu tamanho reduzido diminuirá o custo de atualização das estatísticas.
Redução dos custos de armazenamento do índice.

A criação de um índice filtrado pode reduzir o armazenamento em disco de índices não clusterizados quando um índice de tabela completa não é necessário. É possível substituir um índice não clusterizado de tabela completa por vários índices filtrados sem aumentar de forma significativa os requisitos de armazenamento.

Considerações sobre o design

Quando a coluna tem apenas uma pequena quantidade de valores relevantes para consultas, você pode criar um índice filtrado no subconjunto de valores. O índice resultante será menor e sua manutenção será menos dispendiosa em comparação com um índice não clusterizado de tabela completa definido nas mesmas colunas de chave.

Por exemplo, considere um índice filtrado nos cenários de dados a seguir. Em cada caso, a cláusula WHERE da consulta deve ser um subconjunto da cláusula WHERE de um índice filtrado para se beneficiar do índice filtrado.

Quando os valores em uma coluna são principalmente NULL e a consulta seleciona apenas os valores não NULL. Você pode criar um índice filtrado para as linhas de dados não NULL.
Quando as linhas em uma tabela são marcadas como processadas por um processo de fila ou fluxo de trabalho recorrente. Ao longo do tempo, a maioria das linhas na tabela será marcada como processada. Um índice filtrado em linhas que ainda não foram processadas beneficiaria a consulta recorrente que procura linhas que ainda não foram processadas.
Quando uma tabela tem linhas de dados heterogêneos. Você pode criar um índice filtrado para uma ou mais categorias de dados. Isso pode melhorar o desempenho das consultas nessas linhas de dados limitando o foco de uma consulta a uma área específica da tabela. Novamente, o índice resultante será menor e sua manutenção será menos dispendiosa em comparação com um índice não clusterizado de tabela completa.

Limitações e restrições

Não é possível criar um índice filtrado em uma exibição. No entanto, o otimizador de consulta pode se beneficiar do índice filtrado definido em uma tabela referenciada em uma exibição. O otimizador de consulta considera um índice filtrado para uma consulta que seleciona uma exibição se os resultados da consulta estiverem corretos.
Não é possível criar um índice filtrado em uma tabela quando a coluna acessada na expressão de filtro é de um tipo de dados CLR.
Os índices filtrados têm as seguintes vantagens em relação às exibições indexadas:
- Redução dos custos de manutenção do índice. Por exemplo, o processador de consulta usa menos recursos da CPU para atualizar um índice filtrado do que uma exibição indexada.
- Melhor qualidade de plano. Por exemplo, durante a compilação de uma consulta, o otimizador de consulta considera usar um índice filtrado em mais situações do que a exibição indexada equivalente.
- Recriações de índice online. É possível recriar índices filtrados enquanto estão disponíveis para consultas. As recriações de índices online não têm suporte para exibições indexadas. Para mais informações, veja a opção REBUILD para ALTER INDEX (Transact-SQL).
- Índices não exclusivos. Os índices filtrados podem ser não exclusivos, enquanto as exibições indexadas devem ser exclusivas.
Os índices filtrados são definidos em uma tabela e são compatíveis apenas com operadores de comparação simples. Se você precisar de uma expressão de filtro que referencie várias tabelas ou que tenha uma lógica complexa, deverá criar uma exibição. Os índices filtrados não são compatíveis com operadores LIKE.
A coluna na expressão do índice filtrado não precisará ser uma coluna de chave ou incluída na definição do índice filtrado, se a expressão do índice filtrado for equivalente ao predicado da consulta e a consulta não retorna a coluna na expressão do índice filtrado com os resultados da consulta.
A coluna na expressão de índice filtrado deverá ser uma coluna de chave ou incluída na definição do índice filtrado se o predicado de consulta usar a coluna em uma comparação que não for equivalente à expressão do índice filtrado.
A coluna na expressão do índice filtrado deverá ser uma coluna de chave ou incluída na definição do índice filtrado se fizer parte do conjunto de resultados da consulta.
A chave de índice clusterizado da tabela não precisa ser uma coluna de chave ou incluída na definição do índice filtrado. A chave de índice clusterizado é incluída automaticamente em todos os índices não clusterizados, inclusive índices filtrados. Saiba mais no guia de arquitetura e design de índice.
Se o operador de comparação especificado na expressão do índice filtrado resultar em uma conversão de dados implícita ou explícita, ocorrerá um erro se a conversão ocorrer à esquerda do operador de comparação. Uma solução seria gravar a expressão do índice filtrado com o operador de conversão de dados (CAST ou CONVERT) à direita do operador de comparação.
Examine as opções SET necessárias para a criação de índice filtrado na sintaxe CREATE INDEX (Transact-SQL)
Os filtros não podem ser aplicados à chave primária ou a restrições exclusivas, mas podem ser aplicados a índices com a propriedade UNIQUE.
Não é possível criar um índice filtrado em uma coluna computada.

Permissões

Requer a permissão ALTER na tabela ou exibição. O usuário precisa ser membro da função de servidor fixa sysadmin ou das funções de banco de dados fixas db_ddladmin e db_owner. Para modificar a expressão de índice filtrada, use CREATE INDEX WITH DROP_EXISTING.

Criar um índice filtrado com SSMS

No Pesquisador de Objetos, selecione o sinal de adição para expandir o banco de dados que contém a tabela na qual você deseja criar um índice filtrado.
Selecione o sinal de adição para expandir a pasta Tabelas.
Selecione o sinal de adição para expandir a tabela na qual você deseja criar um índice filtrado.
Clique com o botão direito do mouse na pasta Índices, aponte para Novo Índice e selecione Índice Não Clusterizado....
Na caixa de diálogo Novo Índice , na página Geral , insira o nome do novo índice na caixa Nome do índice .
Em Colunas de chave de índice, selecione Adicionar....
Na caixa de diálogo Selecionar colunas de table_name, marque as caixas de seleção das colunas da tabela a serem adicionadas ao índice.
Selecione OK.
Na página Filtro, em Expressão de Filtro, digite a expressão SQL que você usará para criar o índice filtrado.
Selecione OK.

Criar um índice filtrado com o Transact-SQL

Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.

No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de Dados.
Na barra Padrão, selecione Nova Consulta.
Copie e cole o exemplo a seguir na janela de consulta e selecione Executar.

USE AdventureWorks2022;
GO

DROP INDEX IF EXISTS FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

O índice filtrado FIBillOfMaterialsWithEndDate é válido para a consulta a seguir. Você pode exibir o plano de execução da consulta para determinar se o otimizador de consulta usou o índice filtrado.

USE AdventureWorks2022;
GO

SELECT ProductAssemblyID, ComponentID, StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
    AND ComponentID = 5
    AND StartDate > '01/01/2008' ;
GO

Próximas etapas

Para saber mais sobre como criar índices e conceitos relacionados, confira os seguintes artigos:

Compartilhar via