Partilhar via

Criar índices filtrados

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

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

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

  1. Melhor desempenho de consultas e qualidade dos planos.

    Um índice filtrado bem projetado melhora o desempenho da consulta e a qualidade do plano de execução porque é menor do que um índice não clusterizado de tabela completa e tem estatísticas filtradas. As estatísticas filtradas são mais precisas do que as estatísticas de tabela completa porque cobrem apenas as linhas do índice filtrado.

  2. Redução dos custos de manutenção do índice.

    Um índice é mantido somente quando as instruções DML (linguagem de manipulação de dados) afetam os dados no índice. Um índice filtrado reduz os custos de manutenção do índice em comparação com um índice não clusterizado de tabela completa porque é menor e só é mantido quando os dados no índice são alterados. É possível ter um grande número de índices filtrados, especialmente quando eles contêm dados que são alterados com pouca frequência. Da mesma forma, se um índice filtrado contiver apenas os dados modificados com freqüência, o tamanho menor do índice reduz o custo de atualização das estatísticas.

  3. Custos reduzidos de armazenamento de índice.

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

Considerações de design

Quando uma coluna tem apenas alguns valores relevantes para consultas, você pode criar um índice filtrado no subconjunto de valores. O índice resultante será menor e custará menos para manter do que um índice não clusterizado de tabela completa definido nas mesmas colunas de chave.

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

  • Quando os valores em uma coluna são principalmente NULL e a consulta seleciona apenas a partir dos valores não-NULL. Você pode criar um índice filtrado para as linhas de dados não NULL.
  • Quando as linhas de uma tabela são marcadas como processadas por um fluxo de trabalho recorrente ou processo de fila. Com o 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êneas. Você pode criar um índice filtrado para uma ou mais categorias de dados. Isso pode melhorar o desempenho das consultas nessas linhas de dados, restringindo o foco de uma consulta a uma área específica da tabela. Mais uma vez, o índice resultante será menor e custará menos para manter do que um índice não clusterizado de tabela completa.

Limitations

  • Não é possível criar um índice filtrado numa vista. No entanto, o otimizador de consulta pode beneficiar de um índice filtrado definido numa tabela referenciada numa vista. O otimizador de consulta considera um índice filtrado para uma consulta que seleciona de uma exibição se os resultados da consulta estarão 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 consultas usa menos recursos da CPU para atualizar um índice filtrado do que uma exibição indexada.

    • Melhoria da qualidade do plano. Por exemplo, durante a compilação da consulta, o otimizador de consulta considera o uso de um índice filtrado em mais situações do que a exibição indexada equivalente.

    • Reconstruções de índice online. Você pode reconstruir índices filtrados enquanto eles estão disponíveis para consultas. Não há suporte para reconstruções de índice online para exibições indexadas. Para obter mais informações, consulte a opção REBUILD para ALTER INDEX (Transact-SQL).

    • Índices não exclusivos. Os índices filtrados não precisam ser únicos, enquanto as vistas indexadas devem ser únicas.

  • Os índices filtrados são definidos em uma tabela e suportam apenas operadores de comparação de simples. Se você precisar de uma expressão de filtro que faça referência a várias tabelas ou tenha lógica complexa, crie uma exibição. Os índices filtrados não suportam operadores de LIKE.

  • Uma coluna na expressão de índice filtrada não precisa ser uma chave ou coluna incluída na definição de índice filtrado se a expressão de índice filtrada for equivalente ao predicado de consulta e a consulta não retornar a coluna na expressão de índice filtrada com os resultados da consulta.

  • Uma coluna na expressão de índice filtrada deve ser uma chave ou uma coluna incluída na definição de índice filtrada se o predicado de consulta usar a coluna em uma comparação que não seja equivalente à expressão de índice filtrada.

  • Uma coluna na expressão de índice filtrada deve ser uma chave ou uma coluna incluída na definição de índice filtrada se a coluna estiver no conjunto de resultados da consulta.

  • A chave de índice clusterizada da tabela não precisa ser uma chave ou coluna incluída na definição de índice filtrado. A chave de índice clusterizada é incluída automaticamente em todos os índices não clusterizados, incluindo índices filtrados. Saiba mais no guia de arquitetura e design de índice .

  • Se o operador de comparação especificado na expressão de índice filtrado do índice filtrado resultar em uma conversão de dados implícita ou explícita, ocorrerá um erro se a conversão ocorrer no lado esquerdo de um operador de comparação. Uma solução é escrever a expressão de índice filtrada com o operador de conversão de dados (CAST ou CONVERT) no lado direito do operador de comparação.

  • Revise as opções de SET necessárias para a criação de índice filtrado na sintaxe de CREATE INDEX (Transact-SQL)

  • Os filtros não podem ser aplicados a chaves primárias ou restrições exclusivas, mas podem ser aplicados a índices com a propriedade UNIQUE.

  • Você pode criar um índice filtrado e fazer referência a uma coluna computada na chave ou inclui, mas não pode fazer referência a uma coluna computada na definição de filtro.

Permissions

Requer permissão ALTER na tabela ou exibição. O utilizador deve ser membro das funções fixas de servidor sysadmin ou das funções fixas de base de dados db_ddladmin e db_owner. Para modificar a expressão de índice filtrada, use CREATE INDEX WITH DROP_EXISTING.

Criar um índice filtrado com o SSMS

  1. 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.

  2. Selecione o sinal de mais para expandir a pasta Tabelas.

  3. Selecione o sinal de adição para expandir a tabela na qual você deseja criar um índice filtrado.

  4. Clique com o botão direito do rato na pasta Índices, aponte para Novo Índicee selecione Índice Não Clusterizado....

  5. Na caixa de diálogo Novo Índice, na página Geral, digite o nome do novo índice na caixa Nome do Índice.

  6. Em Colunas de chave de índice, selecione Adicionar....

  7. Na caixa de diálogo Selecionar Colunastable_name, marque a caixa ou caixas de seleção da coluna ou colunas da tabela a serem adicionadas ao índice.

  8. Selecione OK.

  9. Na página Filtro, em Expressão de Filtro, insira a expressão SQL que irá utilizar para criar o índice filtrado.

  10. Selecione OK.

Criar um índice filtrado com Transact-SQL

Os exemplos de código neste artigo usam o banco de dados de exemplo AdventureWorks2025 ou AdventureWorksDW2025, que você pode baixar da home page Microsoft SQL Server Samples and Community Projects.

  1. No Pesquisador de Objetos , conecte-se a uma instância do Mecanismo de Banco de Dados.

  2. Na barra Padrão, selecione Nova Consulta.

  3. 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 uma consulta a seguir. Exiba um plano de execução real 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

Conteúdo relacionado

  • Last updated on