Criar índices filtrados

Aplica-se a:banco de dados SQL Server SQL do AzureInstância Gerenciada de SQL do Azure Azure SynapsePDW (Analytics Analytics Platform System)

Este artigo descreve como criar um índice filtrado usando 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:

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

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

  3. Redução dos custos de armazenamento do í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. É 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 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 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 WHERE cláusula do índice filtrado deve ser um subconjunto da WHERE cláusula de uma consulta 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

  • Você não pode 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.

  • Você não pode 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. Não há suporte para recompilações de índice online para exibições indexadas. Para obter mais informações, consulte a opção REBUILDpara 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. Índices filtrados não dão suporte LIKE a operadores.

  • Uma coluna na expressão de índice filtrada não precisa ser uma chave ou uma coluna incluída na definição de índice filtrada 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 filtrado se o predicado de consulta usar a coluna em uma comparação que não seja equivalente à expressão de índice filtrada.

  • 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 chave ou uma coluna incluída na definição de í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 é gravar 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.

  • Examine as opções necessárias SET 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 UNIQUE propriedade .

  • Você não pode 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

  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 adição 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 mouse na pasta Índices, aponte para Novo Índice e selecione Índice Não Clusterizado... .

  5. Na caixa de diálogo Novo Índice , na página Geral , insira 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 Colunas detable_name , marque a caixa de seleção ou as 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, digite a expressão SQL que você usará para criar o índice filtrado.

  10. Selecione OK.

Criar um índice filtrado com o Transact-SQL

Este exemplo usa o banco de dados AdventureWorks2019, disponível para download em Banco de dados de exemplo do AdventureWorks.

  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 AdventureWorks2019;
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 AdventureWorks2019;
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: