Criar índices filtradosCreate Filtered Indexes

ESTE TÓPICO APLICA-SE A: simSQL Server (começando com o 2016)simBanco de Dados SQL do AzurenãoAzure SQL Data Warehouse nãoParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Este tópico descreve como criar um índice filtrado no SQL Server 2016SQL Server 2016 usando o SQL Server Management StudioSQL Server Management Studio ou o Transact-SQLTransact-SQL.This topic describes how to create a filtered index in SQL Server 2016SQL Server 2016 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. Um índice filtrado é um índice não clusterizado otimizado, criado especialmente para consultas que fazem seleções a partir de um subconjunto bem definido de dados.A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. Ele usa um predicado de filtro para indexar uma parte das linhas da tabela.It uses a filter predicate to index a portion of rows in the table. Um índice filtrado bem projetado pode melhorar 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.A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

Os índices filtrados podem oferecer as seguintes vantagens com relação aos índices de tabela completa:Filtered indexes can provide the following advantages over full-table indexes:

  • Melhor desempenho de consultas e qualidade de planoImproved query performance and plan quality

    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.A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. As estatísticas filtradas são mais precisas do que as estatísticas de tabela completa, pois abrangem apenas as linhas do índice filtrado.The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

  • Redução dos custos de manutenção do índiceReduced index maintenance costs

    A manutenção do índice é feita apenas quando as instruções DML (linguagem de manipulação de dados) afetam os dados do índice.An index is maintained only when data manipulation language (DML) statements affect the data in the index. 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.A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is changed. É possível ter um grande número de índices filtrados, especialmente quando eles contêm dados que são raramente alterados.It is possible to have a large number of filtered indexes, especially when they contain data that is changed infrequently. 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.Similarly, if a filtered index contains only the frequently modified data, the smaller size of the index reduces the cost of updating the statistics.

  • Redução dos custos de armazenamento do índiceReduced index storage costs

    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.Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. É 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.You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

    Neste tópicoIn This Topic

  • Antes de começar:Before you begin:

    Considerações de criaçãoDesign Considerations

    Limitações e restriçõesLimitations and Restrictions

    SegurançaSecurity

  • Para criar um índice filtrado usando:To create a filtered index, using:

    SQL Server Management StudioSQL Server Management Studio

    Transact-SQLTransact-SQL

Antes de começar Before You Begin

Considerações de criação Design Considerations

  • Quando a coluna tem apenas uma pequena quantidade de valores relevantes para consultas, você pode criar um índice filtrado no subconjunto de valores.When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. 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.For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. 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.The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.

  • Quando a tabela contém linhas de dados heterogêneos, é possível criar um índice filtrado para uma ou mais categorias de dados.When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data. Isso pode melhorar o desempenho das consultas nessas linhas de dados limitando o foco de uma consulta a uma área específica da tabela.This can improve the performance of queries on these data rows by narrowing the focus of a query to a specific area of the table. 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.Again, the resulting index will be smaller and cost less to maintain than a full-table nonclustered index.

Limitações e restrições Limitations and Restrictions

  • Não é possível criar um índice filtrado em uma exibição.You cannot create a filtered index on a view. No entanto, o otimizador de consulta pode se beneficiar do índice filtrado definido em uma tabela referenciada em uma exibição.However, the query optimizer can benefit from a filtered index defined on a table that is referenced in a view. O otimizador de consulta considera um índice filtrado para uma consulta que seleciona uma exibição se os resultados da consulta estiverem corretos.The query optimizer considers a filtered index for a query that selects from a view if the query results will be correct.

  • Os índices filtrados têm as seguintes vantagens em relação às exibições indexadas:Filtered indexes have the following advantages over indexed views:

    • Redução dos custos de manutenção do índice.Reduced index maintenance costs. Por exemplo, o processador de consulta usa menos recursos da CPU para atualizar um índice filtrado do que uma exibição indexada.For example, the query processor uses fewer CPU resources to update a filtered index than an indexed view.

    • Melhor qualidade de plano.Improved plan quality. 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.For example, during query compilation, the query optimizer considers using a filtered index in more situations than the equivalent indexed view.

    • Recriações de índice online.Online index rebuilds. É possível recriar índices filtrados enquanto estão disponíveis para consultas.You can rebuild filtered indexes while they are available for queries. As recriações de índices online não têm suporte para exibições indexadas.Online index rebuilds are not supported for indexed views. Para obter mais informações, veja a opção REBUILD de ALTER INDEX (Transact-SQL).For more information, see the REBUILD option for ALTER INDEX (Transact-SQL).

    • Índices não exclusivos.Non-unique indexes. Os índices filtrados podem ser não exclusivos, enquanto as exibições indexadas devem ser exclusivas.Filtered indexes can be non-unique, whereas indexed views must be unique.

  • Os índices filtrados são definidos em uma tabela e oferecem suporte apenas a operadores de comparação simples.Filtered indexes are defined on one table and only support simple comparison operators. 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.If you need a filter expression that references multiple tables or has complex logic, you should create a view.

  • 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.A column in the filtered index expression does not need to be a key or included column in the filtered index definition if the filtered index expression is equivalent to the query predicate and the query does not return the column in the filtered index expression with the query results.

  • 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 usá-la em uma comparação que não for equivalente à expressão do índice filtrado.A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression.

  • 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 column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set.

  • A chave de índice clusterizado da tabela não precisa ser uma coluna de chave ou incluída na definição do índice filtrado.The clustered index key of the table does not need to be a key or included column in the filtered index definition. A chave de índice clusterizado é incluída automaticamente em todos os índices não clusterizados, inclusive índices filtrados.The clustered index key is automatically included in all nonclustered indexes, including filtered indexes.

  • 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.If the comparison operator specified in the filtered index expression of the filtered index results in an implicit or explicit data conversion, an error will occur if the conversion occurs on the left side of a comparison operator. 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.A solution is to write the filtered index expression with the data conversion operator (CAST or CONVERT) on the right side of the comparison operator.

  • Examine as opções SET necessárias para a criação de índice filtrado na sintaxe CREATE INDEX (Transact-SQL)Review the required SET options for filtered index creation in CREATE INDEX (Transact-SQL) syntax

Segurança Security

Permissões Permissions

Requer a permissão ALTER na tabela ou exibição.Requires ALTER permission on the table or view. O usuário deve ser membro da função de servidor fixa sysadmin ou das funções de banco de dados fixas db_ddladmin e db_owner .User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles. Para modificar a expressão de índice filtrado, use CREATE INDEX WITH DROP_EXISTING.To modify the filtered index expression, use CREATE INDEX WITH DROP_EXISTING.

Usando o SQL Server Management Studio Using SQL Server Management Studio

Para criar um índice filtradoTo create a filtered index

  1. No Pesquisador de Objetos, clique no sinal de adição para expandir o banco de dados que contém a tabela na qual você deseja criar um índice filtrado.In Object Explorer, click the plus sign to expand the database that contains the table on which you want to create a filtered index.

  2. Clique no sinal de adição para expandir a pasta Tabelas .Click the plus sign to expand the Tables folder.

  3. Clique no sinal de adição ao lado da tabela na qual você deseja criar um índice filtrado.Click the plus sign to expand the table on which you want to create a filtered index.

  4. Clique com o botão direito do mouse na pasta Índices , aponte para Novo Índicee selecione Índice Não Clusterizado….Right-click the Indexes folder, point to New Index, and select Non-Clustered Index….

  5. Na caixa de diálogo Novo Índice , na página Geral , insira o nome do novo índice na caixa Nome do índice .In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.

  6. Na guia Colunas de chave de índice, clique em Adicionar….Under Index key columns, click Add….

  7. Na caixa de diálogo Selecionar Colunas detable_name , marque as caixas de seleção das colunas da tabela a serem adicionadas ao índice exclusivo.In the Select Columns fromtable_name dialog box, select the check box or check boxes of the table column or columns to be added to the unique index.

  8. Clique em OK.Click OK.

  9. Na página Filtro , em Expressão de Filtro, digite a expressão SQL que você usará para criar o índice filtrado.On the Filter page, under Filter Expression, enter SQL expression that you’ll use to create the filtered index.

  10. Clique em OK.Click OK.

Usando Transact-SQL Using Transact-SQL

Para criar um índice filtradoTo create a filtered index

  1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de DadosDatabase Engine.In Object Explorer, connect to an instance of Mecanismo de Banco de DadosDatabase Engine.

  2. Na barra Padrão, clique em Nova Consulta.On the Standard bar, click New Query.

  3. Copie e cole o exemplo a seguir na janela de consulta e clique em Executar.Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
    GO  
    -- Looks for an existing filtered index named "FIBillOfMaterialsWithEndDate"  
    -- and deletes it from the table Production.BillOfMaterials if found.   
    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  
    -- Creates a filtered index "FIBillOfMaterialsWithEndDate"  
    -- on the table Production.BillOfMaterials   
    -- using the columms ComponentID and StartDate.  
    
    CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate  
        ON Production.BillOfMaterials (ComponentID, StartDate)  
        WHERE EndDate IS NOT NULL ;  
    GO  
    

    O índice filtrado acima é válido para a consulta a seguir.The filtered index above is valid for the following query. Você pode exibir o plano de execução da consulta para determinar se o otimizador de consulta usou o índice filtrado.You can display the query execution plan to determine if the query optimizer used the filtered index.

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

Para garantir que um índice filtrado seja usado em uma consulta SQLTo ensure that a filtered index is used in a SQL query

  1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de DadosDatabase Engine.In Object Explorer, connect to an instance of Mecanismo de Banco de DadosDatabase Engine.

  2. Na barra Padrão, clique em Nova Consulta.On the Standard bar, click New Query.

  3. Copie e cole o exemplo a seguir na janela de consulta e clique em Executar.Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
    GO  
    SELECT ComponentID, StartDate FROM Production.BillOfMaterials  
        WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )   
    WHERE EndDate IN ('20000825', '20000908', '20000918');   
    GO  
    

    Para obter mais informações, veja CREATE INDEX (Transact-SQL).For more information, see CREATE INDEX (Transact-SQL).