Diretrizes de criação de índice filtrado

Quando a coluna tem apenas uma pequena quantidade de valores relevantes para consultas, você pode criar um índice filtrado no subconjunto de valores. Por exemplo, se os valores de uma coluna forem predominantemente NULL e a consulta selecionar apenas valores não NULL, será possível criar um índice filtrado para linhas de dados não NULL. 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, o banco de dados AdventureWorks possui uma tabela Production.BillOfMaterials com 2679 linhas. A coluna EndDate possui apenas 199 linhas com um valor não NULL e as outras 2480 linhas com valor NULL. O índice filtrado a seguir abrange consultas que retornam as colunas definidas no índice e que selecionam apenas linhas com valor não NULL para EndDate.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX 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. Para obter informações sobre como exibir o plano de execução da consulta, consulte Analisando uma consulta.

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

Para obter mais informações sobre como criar índices filtrados e definir a expressão de predicado do índice filtrado, consulte CREATE INDEX (Transact-SQL).

Quando a tabela contém linhas de dados heterogêneos, é possível criar um índice filtrado para uma ou mais categorias de dados.

Por exemplo, a cada produto listado na tabela Production.Product é atribuído um ProductSubcategoryID que, por sua vez, é associado às categorias de produtos Bicicletas, Componentes, Vestuário ou Acessórios. Essas categorias são heterogêneas porque os valores de coluna da tabela Production.Product não estão estritamente correlacionados. Por exemplo, Cor, Ponto de Novo Pedido, Preço da Lista, Peso, Classe e Estilo têm características exclusivas para cada categoria de produto. Suponha que haja consultas freqüentes em Acessórios que possuem as subcategorias 27 a 36. É possível aprimorar o desempenho das consultas de Acessórios criando um índice filtrado nas subcategorias de Acessórios.

O exemplo a seguir cria um índice filtrado em todos os produtos das subcategorias de Acessórios da tabela Production.Product.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIProductAccessories'
    AND object_id = OBJECT_ID ('Production.Product'))
DROP INDEX FIProductAccessories
    ON Production.Product;
GO
CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (ProductSubcategoryID, ListPrice) 
        Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

O índice filtrado FIProductAccessories abrange a consulta a seguir porque os resultados da consulta estão contidos no índice e o plano de consulta não inclui uma pesquisa de tabela base. Por exemplo, a expressão de predicado da consulta ProductSubcategoryID = 33 é um subconjunto do predicado de índice filtrado ProductSubcategoryID >= 27 e ProductSubcategoryID <= 36, as colunas ProductSubcategoryID e ListPrice no predicado de consulta são ambas colunas de chave no índice, e o nome é armazenado no nível folha do índice como uma coluna incluída.

SELECT Name, ProductSubcategoryID, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;
GO

Uma exibição é uma tabela virtual que armazena a definição de uma consulta. Sua finalidade e funcionalidade são mais amplas que as de um índice filtrado. Para obter mais informações sobre exibições, consulte Compreendendo as exibições e Cenários para usar exibições. A tabela a seguir compara algumas funcionalidades permitidas em exibições em relação às de índices filtrados.

*Para lógica de comparação simples em um predicado, consulte a sintaxe da cláusula WHERE em CREATE INDEX.

**Para lógica de comparação complexa em um predicado, consulte a sintaxe da cláusula WHERE para SELECT.

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. O exemplo a seguir cria uma exibição com datas iniciais posteriores a 1º de abril de 2000 e um índice filtrado com datas iniciais posteriores a 1º de agosto de 2000.

USE AdventureWorks;
GO
IF OBJECT_ID ('ViewOnBillOfMaterials') IS NOT NULL
DROP VIEW ViewOnBillOfMaterials;
GO
CREATE VIEW ViewOnBillOfMaterials AS 
SELECT ComponentID, StartDate, EndDate, StartDate + 2 AS ShipDate
FROM Production.BillOfMaterials
WHERE StartDate > '20000401';
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsByStartDate'
    AND object_ID = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsByStartDate 
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsByStartDate
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE StartDate > '20000801';
GO

No exemplo a seguir, a consulta seleciona datas iniciais a partir de 1º de setembro de 2000, todas contidas no índice filtrado e na exibição filtrada. O otimizador de consulta considera o índice filtrado FIBillOfMaterialsByStartDate porque ele contém os resultados corretos para a consulta.

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20000901';
GO

No próximo exemplo, a consulta seleciona datas iniciais posteriores a 1º de junho de 2000, todas contidas na exibição, mas não no índice filtrado. O otimizador de consulta não considera o índice filtrado FIBillOfMaterialsByStartDate porque a consulta pode retornar resultados diferentes usando o índice filtrado em comparação com os resultados corretos quando a consulta utiliza a exibição.

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20000601';
GO

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 obter mais informações, consulte 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.

Pelos motivos descritos acima, quando possível é recomendável usar um índice filtrado em vez de uma exibição indexada. É possível usar um índice filtrado no lugar de uma exibição indexada quando estas condições são atendidas: a exibição faz referência a apenas uma tabela, as consultas não retornam colunas computadas e o predicado da exibição usa lógica de comparação simples. Por exemplo, a expressão de predicado a seguir é permitida em uma definição de exibição, mas não em índices filtrados, pois contém o operador LIKE.

WHERE StartDate > '20000701' AND ModifiedDate LIKE 'E%'

Uma prática recomendada é incluir um pequeno número de colunas de chave ou incluídas em uma definição de índice filtrado e incorporar apenas as colunas necessárias para o otimizador de consulta escolher o índice filtrado para o plano de execução da consulta. O otimizador de consulta pode escolher um índice filtrado para a consulta, independentemente de ele abranger ou não a consulta. No entanto, é mais provável que o otimizador de consulta escolha um índice filtrado se ele abranger a consulta. Para obter mais informações sobre como abranger consultas, consulte Criar índices com colunas incluídas.

Em alguns casos, um índice filtrado abrange a consulta sem incluir as colunas na expressão do índice filtrado como colunas de chave ou incluídas na definição do índice filtrado. As diretrizes a seguir explicam quando uma coluna em uma expressão de índice filtrado deve ser uma coluna de chave ou incluída na definição do índice filtrado. Os exemplos se referem ao índice filtrado, FIBillOfMaterialsWithEndDate, que foi criado anteriormente.

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 retorná-la com os resultados da consulta. Por exemplo, FIBillOfMaterialsWithEndDate abrange a consulta a seguir porque o predicado da consulta é equivalente à expressão do filtro e EndDate não é retornado com os resultados da consulta. FIBillOfMaterialsWithEndDate não precisa de EndDate como uma coluna de chave ou incluída na definição do índice filtrado.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

A coluna na expressão do índice filtrado deverá ser uma coluna de chave ou incluída na definição do índice filtrado se o predicado da consulta usá-la em uma comparação que não for equivalente à expressão do índice filtrado. Por exemplo, FIBillOfMaterialsWithEndDate é válido para a consulta a seguir porque seleciona um subconjunto de linhas do índice filtrado. Contudo, não abrange a consulta a seguir porque EndDate é usado na comparação de EndDate > '20000101', que não é equivalente à expressão do índice filtrado. O processador de consultas não pode executar essa consulta sem observar os valores de EndDate. Portanto, EndDate deve ser uma coluna de chave ou incluída na definição do índice filtrado.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate > '20000101';
GO

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. Por exemplo, FIBillOfMaterialsWithEndDate não abrange a consulta a seguir porque retorna a coluna EndDate nos resultados da consulta. Portanto, EndDate deve ser uma coluna de chave ou incluída na definição do índice filtrado.

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

A chave primária da tabela não precisa ser uma coluna de chave ou incluída na definição do índice filtrado. A chave primária é incluída automaticamente em todos os índices não clusterizados, incluindo índices filtrados.

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.

O exemplo a seguir cria uma tabela com diversos tipos de dados.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.TestTable') IS NOT NULL
DROP TABLE dbo.TestTable;
GO
CREATE TABLE TestTable (a int, b varbinary(4));
GO

Na definição de índice filtrado a seguir, a coluna b é convertida implicitamente em um tipo de dados de número inteiro para comparação com a constante 1. Isso gera a mensagem de erro 10611 porque a conversão ocorre à esquerda do operador no predicado filtrado.

USE AdventureWorks;
GO
IF EXISTS ( SELECT name from sys.indexes 
    WHERE name = N'TestTabIndex'
    AND object_id = OBJECT_ID (N'dbo.TestTable'))
DROP INDEX TestTabIndex on dbo.TestTable
GO
CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = 1;
GO

A solução é converter a constante à direita para o mesmo tipo da coluna b, como mostra o seguinte exemplo:

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = CONVERT(Varbinary(4), 1);
GO

A movimentação da conversão de dados da esquerda para a direita de um operador de comparação pode alterar o significado da conversão. No exemplo anterior, quando o operador CONVERT foi adicionado à direita, a comparação mudou de uma comparação de número inteiro para uma comparação varbinary.

A exibição do catálogo sys.sql_expression_dependencies monitora cada coluna da expressão do índice filtrado como uma dependência de referência. Não é possível descartar, renomear ou alterar a definição de uma coluna de tabela definida em uma expressão de índice filtrado.

Os índices filtrados são úteis quando as colunas contêm subconjuntos de dados bem-definidos, a que as consultas fazem referência em instruções SELECT. São exemplos:

• Colunas esparsas que contêm apenas alguns valores não NULL.

• Colunas heterogêneas que contêm categorias de dados.

• Colunas que contêm intervalos de valores como quantias em dinheiro, hora e datas.

• Partições de tabela definidas pela lógica de comparação simples para obter valores de coluna.

O custo de manutenção reduzido dos índices filtrados é mais perceptível quando o número de linhas do índice é pequeno, se comparado a um índice de tabela completa. Se o índice filtrado incluir a maioria das linhas da tabela, sua manutenção poderá ser mais cara do que a do índice de tabela completa. Nesse caso, você deve usar um índice de tabela completa em vez do índice filtrado.

Os índices filtrados são definidos em uma tabela e oferecem suporte apenas a 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.

Em geral, o Mecanismo de Banco de Dados e as ferramentas oferecem o mesmo suporte para índices filtrados e índices de tabela completa não clusterizados, considerando os índices filtrados como um tipo especial de índices não clusterizados. A lista a seguir apresenta observações sobre ferramentas e recursos que oferecem suporte, que não oferecem suporte ou que oferecem suporte restrito a índices filtrados.

• ALTER INDEX oferece suporte a índices filtrados. Para modificar a expressão de índice filtrado, use CREATE INDEX WITH DROP_EXISTING.

• O recurso de índices ausentes não sugere índices filtrados.

• O Orientador de Otimização do Mecanismo de Banco de Dados considera os índices filtrados ao recomendar a otimização do índice e pode recomendar um índice filtrado com is not null.

• As operações de índice online dão suporte a índices filtrados.

• As dicas de tabela dão suporte a índices filtrados, mas com algumas restrições que não se aplicam a índices não filtrados. Elas serão explicadas na próxima seção.

Em alguns casos, uma consulta com parâmetros não contém informações suficientes no momento da compilação para o otimizador de consulta escolher um índice filtrado. Seria possível reescrever a consulta para fornecer as informações ausentes. No exemplo a seguir, o otimizador de consulta não considera o índice filtrado FIBillOfMaterialsWithComponentID para a instrução SELECT porque os valores de parâmetro de @p e @q são desconhecidos no momento da compilação. O exemplo de consulta a seguir é executado com SHOWPLAN_XML definido como ON para que você possa exibir os índices filtrados sem correspondência para as consultas com parâmetros na saída de SHOWPLAN_XML.

USE AdventureWorks;
GO
IF EXISTS ( SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithComponentID'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE ComponentID IN (533, 324, 753);
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID from Production.BillOfMaterials 
WHERE ComponentID = @p OR ComponentID = @q;
GO
SET SHOWPLAN_XML OFF;
GO

O elemento UnmatchedIndexes e o subelemento Parameterization da saída de SHOWPLAN_XML indicam que o índice filtrado não era uma correspondência da consulta. Para obter informações sobre como exibir a saída de SHOWPLAN_XML, consulte Planos de Execução XML.

A solução é modificar a consulta para que os resultados fiquem vazios quando uma expressão com parâmetros não for um subconjunto do predicado do filtro. A consulta a seguir demonstra essa modificação. A adição da expressão ComponentID in (533, 324, 753) à cláusula WHERE, garante que os resultados da consulta serão um subconjunto da expressão do predicado filtrado. Com essa modificação, o otimizador de consulta pode considerar o índice filtrado FIBillOfMaterialsWithComponentID para a instrução SELECT a seguir.

USE AdventureWorks;
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
WHERE ComponentID in (533, 324, 753)
    AND (ComponentID = @p OR ComponentID = @q);
GO
SET SHOWPLAN_XML OFF;
GO

Parametrização simples

O otimizador de consulta pode usar um índice filtrado, mesmo que ele não abranja a consulta, executando uma pesquisa de chave para recuperar as colunas residuais não abrangidas pelo índice filtrado. Para obter mais informações sobre pesquisas de chave, consulte Operador de plano de execução Key Lookup. O otimizador de consulta poderá optar por essa abordagem se o número estimado de pesquisas de chave for pequeno. A consulta a seguir usa uma dica de índice para forçar o processador de consultas a usar FIBillOfMaterialsWithEndDate com bookmark lookups para EndDate. A pesquisa de chave ocorre para a comparação EndDate > @date no predicado de consulta.

USE AdventureWorks;
GO
DECLARE @date AS DATE;
SET @date = '20000825'
SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WITH ( INDEX (FIBillOfMaterialsWithEndDate) )
WHERE EndDate > @date;
GO

Observe que EndDate > @Date não é uma correspondência exata da expressão de índice filtrado EndDate IS NOT NULL. O índice filtrado ainda é válido para essa consulta com parâmetros porque retorna um subconjunto de linhas definido pela expressão do índice filtrado.