Guia de criação de índice do SQL ServerSQL Server Index Design Guide

Os índices criados inadequadamente e a falta de índices são as principais fontes de gargalos do aplicativo de banco de dados.Poorly designed indexes and a lack of indexes are primary sources of database application bottlenecks. A criação eficiente de índices é muito importante para alcançar um bom desempenho de banco de dados e de aplicativo.Designing efficient indexes is paramount to achieving good database and application performance. Este guia de criação de índice do SQL Server contém informações e práticas recomendadas para ajudar você a criar índices efetivos para atender às necessidades de seu aplicativo.This SQL Server index design guide contains information and best practices to help you design effective indexes to meet the needs of your application.

Aplica-se a: SQL Server 2005SQL Server 2005 a SQL Server 2014SQL Server 2014 , a menos que indicado em contrário.Applies to: SQL Server 2005SQL Server 2005 through SQL Server 2014SQL Server 2014 unless noted otherwise.

Este guia presume que o leitor tenha uma compreensão geral dos tipos de índices disponíveis no SQL ServerSQL Server.This guide assumes the reader has a general understanding of the index types available in SQL ServerSQL Server. Para obter uma descrição geral dos tipos de índices, consulte Tipos de índice.For a general description of index types, see Index Types.

Neste guiaIn This Guide

Noções básicas sobre o Design de índiceIndex Design Basics

Diretrizes para criação de índice geralGeneral Index Design Guidelines

Diretrizes de design de índice clusterizadoClustered Index Design Guidelines

Diretrizes de design de índice não clusterizadoNonclustered Index Design Guidelines

Diretrizes de design de índice exclusivoUnique Index Design Guidelines

Diretrizes de design de índice filtradoFiltered Index Design Guidelines

Leitura adicionalAdditional Reading

Noções básicas sobre criação de índiceIndex Design Basics

Um índice é uma estrutura em disco associada a uma tabela ou exibição, que agiliza a recuperação das linhas de uma tabela ou exibição.An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. Um índice contém chaves criadas de uma ou mais colunas da tabela ou exibição.An index contains keys built from one or more columns in the table or view. Essas chaves são armazenadas em uma estrutura (árvore B) que habilita o SQL Server a localizar a linha ou as linhas associadas aos valores de chave de forma rápida e eficaz.These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

A seleção dos índices certos para um banco de dados e sua carga de trabalho é um ato de balanceamento complexo entre a velocidade de consulta e o custo de atualização.The selection of the right indexes for a database and its workload is a complex balancing act between query speed and update cost. Índices limitados ou com poucas colunas na chave de índice exigem menos espaço em disco e sobrecarga de manutenção.Narrow indexes, or indexes with few columns in the index key, require less disk space and maintenance overhead. Por outro lado, índices amplos cobrem mais consultas.Wide indexes, on the other hand, cover more queries. Talvez você precise experimentar vários projetos diferentes antes de encontrar o índice mais eficiente.You may have to experiment with several different designs before finding the most efficient index. Os índices podem ser adicionados, modificados e descartados sem afetar o esquema de banco de dados ou o design do aplicativo.Indexes can be added, modified, and dropped without affecting the database schema or application design. Portanto, você não deve hesitar em experimentar índices diferentes.Therefore, you should not hesitate to experiment with different indexes.

O otimizador de consulta no SQL ServerSQL Server escolhe o índice mais eficaz de forma confiável na grande maioria dos casos.The query optimizer in SQL ServerSQL Server reliably chooses the most effective index in the vast majority of cases. Sua estratégia geral de criação de índice deveria fornecer uma variedade de opções de índices para o otimizador de consulta escolher e confiar durante a tomada de decisão correta.Your overall index design strategy should provide a variety of indexes for the query optimizer to choose from and trust it to make the right decision. Isso reduz o tempo de análise e atinge um bom desempenho em várias situações.This reduces analysis time and produces good performance over a variety of situations. Para consultar quais índices o otimizador de consulta usa em uma consulta específica, no SQL Server Management StudioSQL Server Management Studio, no menu Consulta , selecione Incluir Plano de Execução Real.To see which indexes the query optimizer uses for a specific query, in SQL Server Management StudioSQL Server Management Studio, on the Query menu, select Include Actual Execution Plan.

Não equipare sempre o uso de índice com bom desempenho e bom desempenho com uso de índice eficiente.Do not always equate index usage with good performance, and good performance with efficient index use. Se o uso de um índice sempre ajudasse a produzir o melhor desempenho, a tarefa do otimizador de consulta seria simples.If using an index always helped produce the best performance, the job of the query optimizer would be simple. Na realidade, a escolha incorreta de um índice pode causar um desempenho insatisfatório.In reality, an incorrect index choice can cause less than optimal performance. Portanto, a tarefa do otimizador de consulta é selecionar um índice ou uma combinação de índices apenas quando isso gerar melhoria de desempenho e evitar a recuperação indexada quando isso atrapalhar o desempenho.Therefore, the task of the query optimizer is to select an index, or combination of indexes, only when it will improve performance, and to avoid indexed retrieval when it will hinder performance.

Tarefas de criação de índiceIndex Design Tasks

As seguintes tarefas compõem a estratégia recomendada para criação de índices:The follow tasks make up our recommended strategy for designing indexes:

  1. Entenda as características do banco de dados.Understand the characteristics of the database itself. Por exemplo, trata-se de um banco de dados OLTP (transação online) com modificações frequentes de dados, um DSS (sistema de apoio à decisão) ou um banco de dados OLAP de data warehouse que contém principalmente dados somente leitura e deve processar conjuntos de dados muito grandes rapidamente.For example, is it an online transaction processing (OLTP) database with frequent data modifications, or a Decision Support System (DSS) or data warehousing (OLAP) database that contains primarily read-only data and must process very large data sets quickly. No SQL Server 2012SQL Server 2012, o columnstore xVelocity de memória otimizada é especialmente apropriado para conjuntos de dados de data warehouse típicos.In SQL Server 2012SQL Server 2012, xVelocity memory optimized columnstore index is especially appropriate for typical data warehousing data sets. Os índices columnstore podem transformar a experiência com data warehouse para usuários proporcionando um desempenho mais rápido para consultas de data warehouse comuns, como filtragem, agregação, agrupamento ou consultas de junção em estrela.Columnstore indexes can transform the data warehousing experience for users by enabling faster performance for common data warehousing queries such as filtering, aggregating, grouping, and star-join queries. Para obter mais informações, consulte índices Columnstore descritos.For more information, see Columnstore Indexes Described.

  2. Entenda as características das consultas mais usadas.Understand the characteristics of the most frequently used queries. Por exemplo, saber que uma consulta usada frequentemente associa duas ou mais tabelas o ajudará a determinar o melhor tipo de índice a ser usado.For example, knowing that a frequently used query joins two or more tables will help you determine the best type of indexes to use.

  3. Entenda as características das colunas usadas nas consultas.Understand the characteristics of the columns used in the queries. Por exemplo, um índice é ideal para colunas que tenham um tipo de dados de inteiro e, também, colunas exclusivas ou não nulas.For example, an index is ideal for columns that have an integer data type and are also unique or nonnull columns. Para colunas que têm subconjuntos bem definido de dados, é possível usar um índice filtrado no SQL Server 2008SQL Server 2008 e versões posteriores.For columns that have well-defined subsets of data, you can use a filtered index in SQL Server 2008SQL Server 2008 and higher versions. Para obter mais informações, consulte Diretrizes de criação de índice filtrado neste guia.For more information, see Filtered Index Design Guidelines in this guide.

  4. Determine quais opções de índice poderiam aumentar o desempenho na criação ou manutenção do índice.Determine which index options might enhance performance when the index is created or maintained. Por exemplo, a criação de um índice clusterizado em uma tabela grande existente se beneficiaria da opção de índice ONLINE.For example, creating a clustered index on an existing large table would benefit from the ONLINE index option. A opção ONLINE permite que atividade simultânea nos dados subjacentes continue enquanto o índice está sendo criado ou reconstruído.The ONLINE option allows for concurrent activity on the underlying data to continue while the index is being created or rebuilt. Para obter mais informações sobre opções de índice, consulte Definir opções de índice.For more information, see Set Index Options.

  5. Determine o melhor local de armazenamento para o índice.Determine the optimal storage location for the index. Um índice não clusterizado pode ser armazenado no mesmo grupo de arquivos que a tabela subjacente ou em um grupo de arquivos diferente.A nonclustered index can be stored in the same filegroup as the underlying table, or on a different filegroup. O local de armazenamento de índices pode melhorar o desempenho de consulta aumentando desempenho de E/S do disco.The storage location of indexes can improve query performance by increasing disk I/O performance. Por exemplo, o armazenamento de um índice não clusterizado em um grupo de arquivos que está em um disco diferente do grupo de arquivos de tabela pode melhorar o desempenho porque vários discos podem ser lidos ao mesmo tempo.For example, storing a nonclustered index on a filegroup that is on a different disk than the table filegroup can improve performance because multiple disks can be read at the same time.

    Alternativamente, os índices clusterizados e não clusterizados podem usar um esquema de partição em vários grupos de arquivos.Alternatively, clustered and nonclustered indexes can use a partition scheme across multiple filegroups. O particionamento facilita o gerenciamento de tabelas ou índices grandes permitindo o acesso ou o gerenciamento de subconjuntos de dados de forma rápida e eficaz, enquanto mantém a integridade geral da coleção.Partitioning makes large tables or indexes more manageable by letting you access or manage subsets of data quickly and efficiently, while maintaining the integrity of the overall collection. Para obter mais informações, consulte Partitioned Tables and Indexes.For more information, see Partitioned Tables and Indexes. Quando você pensar em particionamento, determine se o índice deve ser alinhado; isto é, particionado essencialmente da mesma maneira que a tabela ou particionado de forma independente.When you consider partitioning, determine whether the index should be aligned, that is, partitioned in essentially the same manner as the table, or partitioned independently.

Diretrizes para criação de índice geralGeneral Index Design Guidelines

Administradores de banco de dados experientes podem projetar um bom conjunto de índices, mas essa tarefa é muito complexa, demorada e propensa a erros até mesmo para bancos de dados e cargas de trabalho moderadamente complexos.Experienced database administrators can design a good set of indexes, but this task is very complex, time-consuming, and error-prone even for moderately complex databases and workloads. Compreender as características de seu banco de dados, consultas e colunas de dados pode lhe ajudar a projetar índices melhores.Understanding the characteristics of your database, queries, and data columns can help you design optimal indexes.

Considerações sobre banco de dadosDatabase Considerations

Quando você projeta um índice, considere as seguintes diretrizes para banco de dados:When you design an index, consider the following database guidelines:

  • Números grandes de índices em uma tabela afetam o desempenho das instruções INSERT, UPDATE, DELETE e MERGE porque todos os índices precisam ser ajustados adequadamente à medida que os dados são alterados em uma tabela.Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes. Por exemplo, se uma coluna for usada em vários índices e você executar uma instrução UPDATE que modifica os dados dessa coluna, cada índice que contém essa coluna deverá ser atualizado, bem como a coluna na tabela base subjacente (heap ou índice clusterizado).For example, if a column is used in several indexes and you execute an UPDATE statement that modifies that column's data, each index that contains that column must be updated as well as the column in the underlying base table (heap or clustered index).

    • Evite tabelas fortemente atualizadas em cima desindexações e mantenha os índices estreitos, ou seja, com o mínimo de colunas possível.Avoid over-indexing heavily updated tables and keep indexes narrow, that is, with as few columns as possible.

    • Use muitos índices para aperfeiçoar o desempenho da consulta em tabelas com baixos requisitos de atualização, mas com grandes volumes de dados.Use many indexes to improve query performance on tables with low update requirements, but large volumes of data. Grandes números de índices podem ajudar o desempenho de consultas que não modificam dados, como instruções SELECT, porque o otimizador de consulta tem mais índices para escolher para determinar o método de acesso mais rápido.Large numbers of indexes can help the performance of queries that do not modify data, such as SELECT statements, because the query optimizer has more indexes to choose from to determine the fastest access method.

  • Indexar tabelas pequenas pode não ser bom porque pode fazer o otimizador de consulta levar mais tempo para atravessar o índice procurando dados do que executar uma simples varredura de tabela.Indexing small tables may not be optimal because it can take the query optimizer longer to traverse the index searching for data than to perform a simple table scan. Portanto, os índices em tabelas pequenas talvez nunca sejam usados, mas ainda devem ser mantidos como dados nas alterações de tabela.Therefore, indexes on small tables might never be used, but must still be maintained as data in the table changes.

  • Índices em exibições pode prover ganhos de desempenho significantes quando a exibição contiver agregações, junções de tabela ou uma combinação de agregações e junções.Indexes on views can provide significant performance gains when the view contains aggregations, table joins, or a combination of aggregations and joins. A exibição não precisa estar explicitamente referenciada na consulta para o otimizador de consulta usá-la.The view does not have to be explicitly referenced in the query for the query optimizer to use it.

  • Use o Orientador de Otimização do Mecanismo de Banco de Dados para analisar seu banco de dados e fazer recomendações de índice.Use the Database Engine Tuning Advisor to analyze your database and make index recommendations. Para obter mais informações, consulte Database Engine Tuning Advisor.For more information, see Database Engine Tuning Advisor.

Considerações sobre consultasQuery Considerations

Quando você projeta um índice, considere as seguintes diretrizes para consultas:When you design an index, consider the following query guidelines:

  • Crie índices não clusterizados nas colunas frequentemente usadas em predicados e condições de junção em consultas.Create nonclustered indexes on the columns that are frequently used in predicates and join conditions in queries. No entanto, evite adicionar colunas desnecessárias.However, you should avoid adding unnecessary columns. Acrescentar muitas colunas de índice pode afetar adversamente o espaço em disco e o desempenho de manutenção de índice.Adding too many index columns can adversely affect disk space and index maintenance performance.

  • Cobrindo índices pode melhorar desempenho de consulta porque todos os dados precisaram satisfazer os requisitos da consulta existe dentro do próprio índice.Covering indexes can improve query performance because all the data needed to meet the requirements of the query exists within the index itself. Ou seja, apenas as páginas de índice, e não as páginas de dados da tabela ou do índice clusterizado, são necessárias para recuperar os dados solicitados, portanto reduzindo as operações de E/S gerais do disco.That is, only the index pages, and not the data pages of the table or clustered index, are required to retrieve the requested data; therefore, reducing overall disk I/O. Por exemplo, uma consulta de colunas a e b em uma tabela que tem um índice composto criado em colunas a, be c pode recuperar os dados especificados somente do índice.For example, a query of columns a and b on a table that has a composite index created on columns a, b, and c can retrieve the specified data from the index alone.

  • Escreva consultas que insiram ou modifiquem o máximo de filas possível em uma única instrução, em vez de usar consultas múltiplas para atualizar essas mesmas filas.Write queries that insert or modify as many rows as possible in a single statement, instead of using multiple queries to update the same rows. Ao usar apenas uma instrução, pode-se explorar uma manutenção otimizada do índice.By using only one statement, optimized index maintenance could be exploited.

  • Avalie o tipo da consulta e como as colunas são usadas na consulta.Evaluate the query type and how columns are used in the query. Por exemplo, uma coluna usada em uma consulta de correspondência exata seria uma boa candidata para um índice clusterizado ou não clusterizado.For example, a column used in an exact-match query type would be a good candidate for a nonclustered or clustered index.

Considerações sobre colunasColumn Considerations

Quando você projeta um índice, considere as seguintes diretrizes para as colunas:When you design an index consider the following column guidelines:

  • Mantenha o comprimento da chave de índice curto para os índices clusterizados.Keep the length of the index key short for clustered indexes. Além disso, os índices clusterizados se beneficiam de serem criados em colunas exclusivas ou não nulas.Additionally, clustered indexes benefit from being created on unique or nonnull columns.

  • As colunas que forem do tipo de dados ntext, text, image, varchar(max), nvarchar(max), e varbinary(max) não podem ser especificadas como colunas de chave de índice.Columns that are of the ntext, text, image, varchar(max), nvarchar(max), and varbinary(max) data types cannot be specified as index key columns. Entretanto, os tipos de dados, varchar(max), nvarchar(max), varbinary(max), e xml podem participar em um índice não clusterizados como colunas de índice não chave.However, varchar(max), nvarchar(max), varbinary(max), and xml data types can participate in a nonclustered index as nonkey index columns. Para obter mais informações, consulte a seção 'Índice com colunas incluídas' neste guia.For more information, see the section 'Index with Included Columns' in this guide.

  • Um tipo de dados xml só pode ser uma coluna de chave em um índice XML.An xml data type can only be a key column only in an XML index. Para obter mais informações, veja Índices XML (SQL Server).For more information, see XML Indexes (SQL Server). O SQL Server 2012 SP1 apresenta um novo tipo de índice XML conhecido como um índice XML seletivo.SQL Server 2012 SP1 introduces a new type of XML index known as a Selective XML Index. Esse novo índice pode melhorar o desempenho da consulta dos dados armazenados como XML no SQL Server, permitir uma indexação muito mais rápida de cargas de trabalho de dados XML grandes e melhorar a escalabilidade ao reduzir os custos de armazenamento do próprio índice.This new index can improve querying performance over data stored as XML in SQL Server, allow for much faster indexing of large XML data workloads, and improve scalability by reducing storage costs of the index itself. Para obter mais informações, consulte Índices XML seletivos (SXI).For more information, see Selective XML Indexes (SXI).

  • Examine a singularidade da coluna.Examine column uniqueness. Um índice exclusivo em vez de um índice não exclusivo na mesma combinação de colunas, provê informações adicional para o otimizador de consulta, o que torna o índice mais útil.A unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that makes the index more useful. Para obter mais informações, consulte Diretrizes de design de índice exclusivo neste guia.For more information, see Unique Index Design Guidelines in this guide.

  • Examine a distribuição de dados na coluna.Examine data distribution in the column. Frequentemente, uma consulta longa é causada ao se indexar uma coluna com poucos valores exclusivos, ou ao executar uma junção em tal coluna.Frequently, a long-running query is caused by indexing a column with few unique values, or by performing a join on such a column. Isto é um problema fundamental com dados e consulta, e geralmente não pode ser resolvido sem identificar esta situação.This is a fundamental problem with the data and query, and generally cannot be resolved without identifying this situation. Por exemplo, uma lista telefônica física ordenada alfabeticamente pelo último nome não será rápida em localizar uma pessoa, se todas as pessoas na cidade tiverem nomes de Smith ou Jones.For example, a physical telephone directory sorted alphabetically on last name will not expedite locating a person if all people in the city are named Smith or Jones. Para obter mais informações sobre distribuição de dados, consulte Statistics.For more information about data distribution, see Statistics.

  • Considere o uso de índices filtrados em colunas com subconjuntos bem definidos, por exemplo, colunas esparsas, colunas com grande a maioria dos valores NULL, colunas com categorias de valores e colunas com intervalos diferentes de valores.Consider using filtered indexes on columns that have well-defined subsets, for example sparse columns, columns with mostly NULL values, columns with categories of values, and columns with distinct ranges of values. Um índice filtrado bem projetado pode melhorar o desempenho da consulta e reduzir os custos de manutenção de índice e de armazenamento.A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce storage costs.

  • Considere a ordem das colunas se o índice contiver colunas múltiplas.Consider the order of the columns if the index will contain multiple columns. A coluna que é usada na cláusula WHERE em um critério de consulta igual a (=), maior que (>), menor que (>) ou BETWEEN, ou que participa em uma junção, deve ser posicionada primeiro.The column that is used in the WHERE clause in an equal to (=), greater than (>), less than (<), or BETWEEN search condition, or participates in a join, should be placed first. Colunas adicionais devem ser ordenadas com base em seu nível de distinção, ou seja, do mais distinto ao menos distinto.Additional columns should be ordered based on their level of distinctness, that is, from the most distinct to the least distinct.

    Por exemplo, se o índice for definido como LastName, FirstName o índice será útil quando o critério de consulta for WHERE LastName = 'Smith' ou WHERE LastName = Smith AND FirstName LIKE 'J%'.For example, if the index is defined as LastName, FirstName the index will be useful when the search criterion is WHERE LastName = 'Smith' or WHERE LastName = Smith AND FirstName LIKE 'J%'. Porém, o otimizador de consulta não usaria o índice para uma consulta que tivesse pesquisado apenas em FirstName (WHERE FirstName = 'Jane').However, the query optimizer would not use the index for a query that searched only on FirstName (WHERE FirstName = 'Jane').

  • Considere indexar as colunas computadas.Consider indexing computed columns. Para obter mais informações, consulte Indexes on Computed Columns.For more information, see Indexes on Computed Columns.

Características do índiceIndex Characteristics

Depois de ter determinado que um índice é apropriado para uma consulta, você pode selecionar o tipo de índice que melhor se adéque a sua situação.After you have determined that an index is appropriate for a query, you can select the type of index that best fits your situation. Características de índice incluem o seguinte:Index characteristics include the following:

  • Clusterizado X não clusterizado.Clustered versus nonclustered

  • Exclusivo X não exclusivoUnique versus nonunique

  • Única coluna X multicolunaSingle column versus multicolumn

  • Ordem crescente ou decrescente em colunas no índiceAscending or descending order on the columns in the index

  • Tabela completa versus filtrada para índices não clusterizadosFull-table versus filtered for nonclustered indexes

Você também pode personalizar as características de armazenamento inicial do índice para aperfeiçoar seu desempenho ou manutenção definindo uma opção como FILLFACTOR.You can also customize the initial storage characteristics of the index to optimize its performance or maintenance by setting an option such as FILLFACTOR. Além disso, você pode determinar o local de armazenamento de índice usando grupos de arquivos ou esquemas de partição para aperfeiçoar o desempenho.Also, you can determine the index storage location by using filegroups or partition schemes to optimize performance.

Posição do índice em grupos de arquivos ou esquemas de partiçãoIndex Placement on Filegroups or Partitions Schemes

À medida que desenvolve sua estratégia de design de índices, considere a colocação dos índices nos grupos de arquivos associados ao banco de dados.As you develop your index design strategy, you should consider the placement of the indexes on the filegroups associated with the database. A seleção cuidadosa do grupo de arquivos ou esquema de partição pode melhorar o desempenho da consulta.Careful selection of the filegroup or partition scheme can improve query performance.

Por padrão, os índices são armazenados no mesmo grupo de arquivos que a tabela base na qual o índice é criado.By default, indexes are stored in the same filegroup as the base table on which the index is created. Um índice cluster não particionado e a tabela base sempre residem no mesmo grupo de arquivos.A nonpartitioned clustered index and the base table always reside in the same filegroup. No entanto, você pode fazer o seguinte:However, you can do the following:

  • Crie índices não clusterizados em um grupo de arquivos diferente do grupo de arquivos da tabela base ou do índice clusterizado.Create nonclustered indexes on a filegroup other than the filegroup of the base table or clustered index.

  • Particione índices cluster e não cluster para que ocupem vários grupos de arquivos.Partition clustered and nonclustered indexes to span multiple filegroups.

  • Mova uma tabela de um grupo de arquivos para outro descartando o índice cluster e especificando um novo grupo de arquivos ou esquema de partição na cláusula MOVE TO da instrução DROP INDEX ou usando a instrução CREATE INDEX com a cláusula DROP_EXISTING.Move a table from one filegroup to another by dropping the clustered index and specifying a new filegroup or partition scheme in the MOVE TO clause of the DROP INDEX statement or by using the CREATE INDEX statement with the DROP_EXISTING clause.

Ao criar o índice não cluster em um grupo de arquivos diferente, você pode obter ganhos de desempenho se os grupos de arquivos estiverem usando unidades físicas diferentes com seus próprios controladores.By creating the nonclustered index on a different filegroup, you can achieve performance gains if the filegroups are using different physical drives with their own controllers. As informações de índices e de dados podem ser lidas em paralelo pelas várias cabeças de disco.Data and index information can then be read in parallel by the multiple disk heads. Por exemplo, se Table_A no grupo de arquivos f1 e Index_A no grupo de arquivos f2 estiverem ambos sendo usados pela mesma consulta, podem-se obter ganhos de desempenho porque os dois grupos de arquivos estão sendo completamente usados sem contenção.For example, if Table_A on filegroup f1 and Index_A on filegroup f2 are both being used by the same query, performance gains can be achieved because both filegroups are being fully used without contention. Porém, se Table_A for verificado pela consulta, mas Index_A não for referenciado, apenas o grupo de arquivos f1 será usado.However, if Table_A is scanned by the query but Index_A is not referenced, only filegroup f1 is used. Isso não cria nenhum ganho de desempenho.This creates no performance gain.

Como você não pode prever que tipo de acesso acontecerá e quando ocorrerá, pode ser preferível distribuir suas tabelas e índices por todos os grupos de arquivos.Because you cannot predict what type of access will occur and when it will occur, it could be a better decision to spread your tables and indexes across all filegroups. Isso garantirá que todos os discos estejam sendo acessados, pois todos os dados e índices estarão distribuídos igualmente por todos os discos, independentemente da maneira como os dados sejam acessados.This would guarantee that all disks are being accessed because all data and indexes are spread evenly across all disks, regardless of which way the data is accessed. Essa também é uma abordagem mais simples para os administradores do sistema.This is also a simpler approach for system administrators.

Partições em vários grupos de arquivosPartitions Across Multiple Filegroups

Você também pode considerar o particionamento de índices cluster e não cluster em vários grupos de arquivos.You can also consider partitioning clustered and nonclustered indexes across multiple filegroups. Os índices particionados são particionados horizontalmente, ou por linha, com base na função de uma partição.Partitioned indexes are partitioned horizontally, or by row, based on a partition function. A função da partição define como cada linha é mapeada para um conjunto de partições, com base nos valores de certas colunas, chamadas colunas de particionamento.The partition function defines how each row is mapped to a set of partitions based on the values of certain columns, called partitioning columns. Um esquema de partição especifica o mapeamento das partições para um conjunto de grupos de arquivos.A partition scheme specifies the mapping of the partitions to a set of filegroups.

O particionamento de um índice pode proporcionar os seguintes benefícios:Partitioning an index can provide the following benefits:

  • Proporcionar sistemas evolutivos que tornam grandes índices mais gerenciáveis.Provide scalable systems that make large indexes more manageable. Sistemas OLTP, por exemplo, podem implementar aplicativos que reconhecem partição que tratam de índices grandes.OLTP systems, for example, can implement partition-aware applications that deal with large indexes.

  • Fazer as consultas serem executadas de maneira mais rápida e eficiente.Make queries run faster and more efficiently. Quando as consultas acessarem várias partições de um índice, o otimizador de consulta pode processar partições individuais ao mesmo tempo e pode excluir partições que não sejam afetadas pela consulta.When queries access several partitions of an index, the query optimizer can process individual partitions at the same time and exclude partitions that are not affected by the query.

Para saber mais, confira Partitioned Tables and Indexes.For more information, see Partitioned Tables and Indexes.

Diretrizes de criação de ordem de classificação de índiceIndex Sort Order Design Guidelines

Ao definir índices, confirme se os dados da coluna de chave de índice deverão ser armazenados em ordem crescente ou decrescente.When defining indexes, you should consider whether the data for the index key column should be stored in ascending or descending order. Ordem crescente é o padrão e assegura a compatibilidade com as versões anteriores do SQL ServerSQL Server.Ascending is the default and maintains compatibility with earlier versions of SQL ServerSQL Server. A sintaxe das instruções CREATE INDEX, CREATE TABLE e ALTER TABLE dá suporte às palavras-chave ASC (crescente) e DESC (decrescente) em colunas individuais de índices e restrições.The syntax of the CREATE INDEX, CREATE TABLE, and ALTER TABLE statements supports the keywords ASC (ascending) and DESC (descending) on individual columns in indexes and constraints.

A especificação da ordem de armazenamento dos valores de chave em um índice é útil quando as consultas que fazem referência à tabela contêm cláusulas ORDER BY que especificam direcionamentos diferentes para a coluna de chave ou as colunas daquele índice.Specifying the order in which key values are stored in an index is useful when queries referencing the table have ORDER BY clauses that specify different directions for the key column or columns in that index. Nesses casos, o índice pode eliminar a necessidade de um operador SORT no plano de consulta, o que torna a consulta mais eficaz.In these cases, the index can remove the need for a SORT operator in the query plan; therefore, this makes the query more efficient. Por exemplo, os compradores do departamento de compras da Ciclos da Adventure WorksAdventure Works Cycles devem avaliar a qualidade dos produtos que adquirem de fornecedores.For example, the buyers in the Ciclos da Adventure WorksAdventure Works Cycles purchasing department have to evaluate the quality of products they purchase from vendors. Os compradores estão mais interessados em localizar os produtos enviados por esses fornecedores, e que têm alta taxa de rejeição.The buyers are most interested in finding products sent by these vendors with a high rejection rate. Como demonstrado pela consulta a seguir, recuperar os dados para atender esses critérios requer que a coluna RejectedQty da tabela Purchasing.PurchaseOrderDetail seja classificada em ordem decrescente (do maior para o menor) e que a coluna ProductID seja classificada em ordem crescente (do menor para o maior).As shown in the following query, retrieving the data to meet this criteria requires the RejectedQty column in the Purchasing.PurchaseOrderDetail table to be sorted in descending order (large to small) and the ProductID column to be sorted in ascending order (small to large).

SELECT RejectedQty, ((RejectedQty/OrderQty)*100) AS RejectionRate,  
    ProductID, DueDate  
FROM Purchasing.PurchaseOrderDetail  
ORDER BY RejectedQty DESC, ProductID ASC;  

O plano de execução a seguir, dessa consulta, mostra que o otimizador de consultas usou um operador SORT para retornar o conjunto de resultados na ordem especificada pela cláusula ORDER BY.The following execution plan for this query shows that the query optimizer used a SORT operator to return the result set in the order specified by the ORDER BY clause.

Plano de execução mostra uma classificação operador é usado. Execution plan shows a SORT operator is used.

Se um índice for criado com colunas de chave correspondentes às da cláusula ORDER BY da consulta, o operador SORT poderá ser eliminado do plano de consulta, e este se tornará mais eficaz.If an index is created with key columns that match those in the ORDER BY clause in the query, the SORT operator can be eliminated in the query plan and the query plan is more efficient.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty  
ON Purchasing.PurchaseOrderDetail  
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);  

Depois que a consulta for novamente executada, o plano de execução a seguir mostra que o operador SORT foi eliminado e que o índice não clusterizado recentemente criado é utilizado.After the query is executed again, the following execution plan shows that the SORT operator has been eliminated and the newly created nonclustered index is used.

Plano de execução mostra uma classificação de operador não é usadoExecution plan shows a SORT operator is not used

O Mecanismo de Banco de DadosDatabase Engine pode se mover para qualquer direção de forma igualmente eficaz.The Mecanismo de Banco de DadosDatabase Engine can move equally efficiently in either direction. Um índice definido como (RejectedQty DESC, ProductID ASC) ainda pode ser usado em uma consulta na qual a direção de classificação das colunas da cláusula ORDER BY é invertida.An index defined as (RejectedQty DESC, ProductID ASC) can still be used for a query in which the sort direction of the columns in the ORDER BY clause are reversed. Por exemplo, uma consulta com a cláusula ORDER BY ORDER BY RejectedQty ASC, ProductID DESC pode utilizar o índice.For example, a query with the ORDER BY clause ORDER BY RejectedQty ASC, ProductID DESC can use the index.

A ordem de classificação só pode ser especificada para colunas de chave.Sort order can be specified only for key columns. A exibição de catálogo sys.index_columns e a função INDEXKEY_PROPERTY relatam se a coluna de índice está armazenada em ordem crescente ou decrescente.The sys.index_columns catalog view and the INDEXKEY_PROPERTY function report whether an index column is stored in ascending or descending order.

Ícone de seta usado com o link voltar ao início neste guiaArrow icon used with Back to Top link In This Guide

Diretrizes de design de índices clusterizadosClustered Index Design Guidelines

Os índices clusterizados classificam e armazenam as linhas de dados da tabela com base em seus valores de chave.Clustered indexes sort and store the data rows in the table based on their key values. Pode haver apenas um índice clusterizado por tabela, porque as próprias linhas de dados podem ser classificadas apenas em uma única ordem.There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. Com poucas exceções, toda tabela deveria ter um índice clusterizado definido na coluna ou colunas, o qual proporciona o seguinte:With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following:

  • Pode ser usado para consultas frequentemente usadas.Can be used for frequently used queries.

  • Oferece um alto grau de singularidade.Provide a high degree of uniqueness.

    Observação

    Quando você cria uma restrição PRIMARY KEY, um índice exclusivo na coluna, ou colunas, é criado automaticamente.When you create a PRIMARY KEY constraint, a unique index on the column, or columns, is automatically created. Por padrão, esse índice é cluster. Porém, você pode especificar um índice não clusterizado ao criar a restrição.By default, this index is clustered; however, you can specify a nonclustered index when you create the constraint.

  • Pode ser usado em consultas de intervalo.Can be used in range queries.

Se o índice clusterizado não for criado com a propriedade UNIQUE, o Mecanismo de Banco de DadosDatabase Engine acrescentará uma coluna de indicador de exclusividade de 4 bytes automaticamente à tabela.If the clustered index is not created with the UNIQUE property, the Mecanismo de Banco de DadosDatabase Engine automatically adds a 4-byte uniqueifier column to the table. Quando necessário, o Mecanismo de Banco de DadosDatabase Engine acrescenta um valor de indicador de exclusividade automaticamente a uma linha para tornar cada chave exclusiva.When it is required, the Mecanismo de Banco de DadosDatabase Engine automatically adds a uniqueifier value to a row to make each key unique. Essa coluna e seus valores são usados internamente e não podem ser vistos ou avaliados por usuários.This column and its values are used internally and cannot be seen or accessed by users.

Arquitetura de índice clusterizadoClustered Index Architecture

No SQL ServerSQL Server, os índices são organizados como árvores B.In SQL ServerSQL Server, indexes are organized as B-trees. Cada página em uma árvore B de índice é chamada de nó do índice.Each page in an index B-tree is called an index node. O nó superior da árvore B é chamado de nó raiz.The top node of the B-tree is called the root node. Os nós inferiores no índice são chamados de nós folha.The bottom nodes in the index are called the leaf nodes. Quaisquer níveis de índice entre os nós raiz e folha são coletivamente conhecidos como níveis intermediários.Any index levels between the root and the leaf nodes are collectively known as intermediate levels. Em um índice clusterizado, os nós folha contêm as páginas de dados da tabela subjacente.In a clustered index, the leaf nodes contain the data pages of the underlying table. Os nós de nível intermediário e raiz contêm páginas de índice com linhas de índice.The root and intermediate level nodes contain index pages holding index rows. Cada linha de índice contém um valor de chave e um ponteiro para uma página de nível de intermediário na árvore B ou uma linha de dados no nível folha do índice.Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. As páginas de cada nível do índice são vinculadas a uma lista vinculada duas vezes.The pages in each level of the index are linked in a doubly-linked list.

Índices clusterizados têm uma linha em sys.partitions, com index_id = 1 para cada partição usada pelo índice.Clustered indexes have one row in sys.partitions, with index_id = 1 for each partition used by the index. Por padrão, um índice clusterizado tem um único particionamento.By default, a clustered index has a single partition. Quando um índice clusterizado tem particionamentos múltiplos, cada particionamento tem uma estrutura de árvore B que contém os dados para aquele particionamento específico.When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition. Por exemplo, se um índice clusterizado tiver quatro particionamentos, haverá quatro estruturas de árvore B; uma em cada particionamento.For example, if a clustered index has four partitions, there are four B-tree structures; one in each partition.

Dependendo dos tipos de dados no índice clusterizado, cada estrutura de índice clusterizado terá uma ou mais unidades de alocação para armazenar e gerenciar os dados de um particionamento específico.Depending on the data types in the clustered index, each clustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. No mínimo, cada índice clusterizado terá uma unidade de alocação IN_ROW_DATA por particionamento.At a minimum, each clustered index will have one IN_ROW_DATA allocation unit per partition. O índice clusterizado também terá uma unidade de alocação LOB_DATA por particionamento se contiver colunas LOB (objetos grandes).The clustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns. Também terá uma unidade de alocação ROW_OVERFLOW_DATA por particionamento se tiver colunas de comprimento variável excedendo o limite de tamanho de linha de 8.060 bytes.It will also have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit.

As páginas da cadeia de dados e as linhas são classificadas pelo valor da chave de índice clusterizado.The pages in the data chain and the rows in them are ordered on the value of the clustered index key. Todas as inserções são feitas no ponto em que o valor de chave da linha inserida se ajusta à sequência de classificação entre as linhas existentes.All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows.

Esta ilustração mostra a estrutura de um índice clusterizado em um único particionamento.This illustration shows the structure of a clustered index in a single partition.

Níveis de um índice clusterizadoLevels of a clustered index

Considerações sobre consultasQuery Considerations

Antes de criar índices clusterizados, entenda como seus dados serão acessados.Before you create clustered indexes, understand how your data will be accessed. Considere utilizar um índice clusterizado para consultas que façam o seguinte:Consider using a clustered index for queries that do the following:

  • Retornam um intervalo de valores usando os operadores como BETWEEN, >, >=, < e <=.Return a range of values by using operators such as BETWEEN, >, >=, <, and <=.

    Depois que a linha com o primeiro valor for encontrada usando o índice cluster, garante-se que as linhas com valores indexados subsequentes estejam fisicamente adjacentes.After the row with the first value is found by using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. Por exemplo, se uma consulta recuperar registros entre um intervalo de números de ordem de vendas, um índice clusterizado na coluna SalesOrderNumber poderá localizar rapidamente a linha que contém o número de ordem de vendas inicial e em seguida recuperará todas as linhas sucessivas na tabela, até que o último número de ordem de vendas seja alcançado.For example, if a query retrieves records between a range of sales order numbers, a clustered index on the column SalesOrderNumber can quickly locate the row that contains the starting sales order number, and then retrieve all successive rows in the table until the last sales order number is reached.

  • Retornam grandes conjuntos de resultados.Return large result sets.

  • Use cláusulas JOIN. Normalmente elas são colunas de chave estrangeira.Use JOIN clauses; typically these are foreign key columns.

  • Use cláusulas ORDER BY ou GROUP BY.Use ORDER BY, or GROUP BY clauses.

    Um índice nas colunas especificadas na cláusula ORDER BY ou GROUP BY pode eliminar a necessidade de o Mecanismo de Banco de DadosDatabase Engine classificar os dados, pois as linhas já estão classificadas.An index on the columns specified in the ORDER BY or GROUP BY clause may remove the need for the Mecanismo de Banco de DadosDatabase Engine to sort the data, because the rows are already sorted. Isso melhora o desempenho da consulta.This improves query performance.

Considerações sobre colunasColumn Considerations

Geralmente, você deve definir a chave de índice clusterizado com o menor número de colunas possível.Generally, you should define the clustered index key with as few columns as possible. Considere colunas que tenham um ou mais dos seguintes atributos:Consider columns that have one or more of the following attributes:

  • Sejam exclusivas ou contenham muitos valores distintosAre unique or contain many distinct values

    Por exemplo, uma ID de funcionário identifica os funcionários de maneira exclusiva.For example, an employee ID uniquely identifies employees. Um índice clusterizado ou restrição PRIMARY KEY na coluna EmployeeID melhoraria o desempenho de consultas que pesquisam informações de funcionário com base no número de ID do funcionário.A clustered index or PRIMARY KEY constraint on the EmployeeID column would improve the performance of queries that search for employee information based on the employee ID number. Como alternativa, um índice clusterizado poderia ser criado em LastName, FirstName, MiddleName porque os registros dos funcionários são agrupados e consultados frequentemente dessa maneira e a combinação dessas colunas ainda ofereceria um grau alto de diferença.Alternatively, a clustered index could be created on LastName, FirstName, MiddleName because employee records are frequently grouped and queried in this way, and the combination of these columns would still provide a high degree of difference.

  • Sejam acessadas sequencialmenteAre accessed sequentially

    Por exemplo, um ID de produto identifica produtos de maneira exclusiva na tabela Production.Product no banco de dados AdventureWorks2012AdventureWorks2012 .For example, a product ID uniquely identifies products in the Production.Product table in the AdventureWorks2012AdventureWorks2012 database. Consultas nas quais uma pesquisa sequencial seja especificada, tais como WHERE ProductID BETWEEN 980 and 999, se beneficiariam de um índice clusterizado em ProductID.Queries in which a sequential search is specified, such as WHERE ProductID BETWEEN 980 and 999, would benefit from a clustered index on ProductID. Isso ocorre porque as linhas seriam armazenadas em ordem classificada nessa coluna de chave.This is because the rows would be stored in sorted order on that key column.

  • Definido como IDENTITY.Defined as IDENTITY.

  • Frequentemente usado para classificar os dados recuperados de uma tabela.Used frequently to sort the data retrieved from a table.

    Pode ser uma boa ideia agrupar, ou seja, classificar fisicamente, a tabela nessa coluna para economizar o custo de uma operação de classificação toda vez que a coluna for consultada.It can be a good idea to cluster, that is physically sort, the table on that column to save the cost of a sort operation every time the column is queried.

Índices clusterizados não são uma boa escolha para os seguintes atributos:Clustered indexes are not a good choice for the following attributes:

  • Colunas que sofrem mudanças frequentesColumns that undergo frequent changes

    Isso faz com que uma fila inteira se mova, pois o Mecanismo de Banco de DadosDatabase Engine deve manter os valores de dados de uma linha em ordem física.This causes in the whole row to move, because the Mecanismo de Banco de DadosDatabase Engine must keep the data values of a row in physical order. Essa é uma consideração importante em sistemas de processamento de transações de alto volume nos quais os dados sejam normalmente voláteis.This is an important consideration in high-volume transaction processing systems in which data is typically volatile.

  • Chaves largasWide keys

    Chaves largas são uma combinação de várias colunas ou de várias colunas de tamanho grande.Wide keys are a composite of several columns or several large-size columns. Os valores de chave do índice clusterizado são usados por todos os índices não clusterizados como chaves de pesquisa.The key values from the clustered index are used by all nonclustered indexes as lookup keys. Qualquer índice não clusterizado definido na mesma tabela será significativamente maior porque as entradas de índice não clusterizado contêm a chave de cluster e também as colunas de chave definidas para aquele índice não clusterizado.Any nonclustered indexes defined on the same table will be significantly larger because the nonclustered index entries contain the clustering key and also the key columns defined for that nonclustered index.

Ícone de seta usado com o link voltar ao início neste guiaArrow icon used with Back to Top link In This Guide

Diretrizes de criação de índice não clusterizadoNonclustered Index Design Guidelines

Um índice não clusterizado contém os valores de chave do índice e os localizadores de linha que apontam para o local de armazenamento dos dados da tabela.A nonclustered index contains the index key values and row locators that point to the storage location of the table data. Você pode criar vários índices não clusterizados em uma tabela ou exibição indexada.You can create multiple nonclustered indexes on a table or indexed view. Em geral, os índices não clusterizados devem ser criados para aprimorar o desempenho de consultas utilizadas com frequência que não são cobertas pelo índice clusterizado.Generally, nonclustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index.

Semelhante à maneira como o índice de um livro é usado, o otimizador de consulta procura um valor de dados pesquisando o índice não clusterizado para encontrar o local do valor de dados na tabela e, depois, recupera os dados diretamente daquele local.Similar to the way you use an index in a book, the query optimizer searches for a data value by searching the nonclustered index to find the location of the data value in the table and then retrieves the data directly from that location. Isso faz com que os índices não clusterizados sejam a opção ideal para consultas de correspondência exata, uma vez que o índice contém entradas que descrevem o local preciso na tabela dos valores de dados pesquisados pelas consultas.This makes nonclustered indexes the optimal choice for exact match queries because the index contains entries describing the exact location in the table of the data values being searched for in the queries. Por exemplo, para consultar a tabela HumanResources. Employee de todos os funcionários que reportam para um determinado gerente, o otimizador de consulta pode usar o índice não clusterizado IX_Employee_ManagerID, que tem ManagerID como sua coluna de chave.For example, to query the HumanResources. Employee table for all employees that report to a specific manager, the query optimizer might use the nonclustered index IX_Employee_ManagerID; this has ManagerID as its key column. O otimizador de consulta pode localizar rapidamente todas as entradas no índice que correspondem ao ManagerIDespecificado.The query optimizer can quickly find all entries in the index that match the specified ManagerID. Cada entrada do índice aponta para a página e a linha exatas na tabela ou índice clusterizado, em que os dados correspondentes podem ser localizados.Each index entry points to the exact page and row in the table, or clustered index, in which the corresponding data can be found. Depois que o otimizador de consulta localizar todas as entradas no índice, poderá ir diretamente para a página e a linha exatas e recuperar os dados.After the query optimizer finds all entries in the index, it can go directly to the exact page and row to retrieve the data.

Arquitetura de índice não clusterizadoNonclustered Index Architecture

Os índices não clusterizados têm a mesma estrutura de árvore B que os índices clusterizados, com exceção das seguintes diferenças significativas:Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:

  • As linhas de dados da tabela subjacente não são classificadas nem armazenadas em ordem com base nas suas chaves não clusterizadas.The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.

  • A camada de folha de um índice não clusterizado é constituída de páginas de índice, em vez de páginas de dados.The leaf layer of a nonclustered index is made up of index pages instead of data pages.

Os localizadores de linha, em linhas de índice não clusterizado, são um ponteiro para uma linha ou uma chave de índice clusterizado para uma linha, como descrito a seguir.The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:

  • Se a tabela for um heap, ou seja, se não tiver um índice clusterizado, o localizador de linha será um ponteiro para a linha.If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. O ponteiro é criado a partir do ID (identificador), do número da página e do número da linha na página do arquivo.The pointer is built from the file identifier (ID), page number, and number of the row on the page. O ponteiro inteiro é conhecido como RID (Identificação de Linha).The whole pointer is known as a Row ID (RID).

  • Se a tabela tiver um índice clusterizado, ou o índice estiver em uma exibição indexada, o localizador de linha será a chave de índice clusterizado da linha.If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row.

Os índices não clusterizados têm uma linha em sys.partitions com index_id >1 para cada partição usada pelo índice.Nonclustered indexes have one row in sys.partitions with index_id >1 for each partition used by the index. Por padrão, um índice não clusterizado tem uma única partição.By default, a nonclustered index has a single partition. Quando um índice não clusterizado tem várias partições, cada partição tem uma estrutura de árvore B que contém linhas de índice para aquela partição específica.When a nonclustered index has multiple partitions, each partition has a B-tree structure that contains the index rows for that specific partition. Por exemplo, se um índice não clusterizado tiver quatro partições, haverá quatro estruturas de árvore B, uma em cada partição.For example, if a nonclustered index has four partitions, there are four B-tree structures, with one in each partition.

Dependendo dos tipos de dados no índice não clusterizado, cada estrutura de índice não clusterizado terá uma ou mais unidades de alocação para armazenar e gerenciar os dados de uma partição específica.Depending on the data types in the nonclustered index, each nonclustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. No mínimo, cada índice não clusterizado terá uma unidade de alocação IN_ROW_DATA por partição que armazena as páginas de árvore B do índice.At a minimum, each nonclustered index will have one IN_ROW_DATA allocation unit per partition that stores the index B-tree pages. O índice não clusterizado também terá uma unidade de alocação LOB_DATA por partição se contiver colunas LOB (objetos grandes).The nonclustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns . Além disso, terá uma unidade de alocação ROW_OVERFLOW_DATA por partição se contiver colunas de comprimento variável que excedem o limite de tamanho de linha de 8.060 bytes.Additionally, it will have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit.

A ilustração a seguir mostra a estrutura de um índice não clusterizado em uma única partição.The following illustration shows the structure of a nonclustered index in a single partition.

Níveis de um índice não clusterizadoLevels of a nonclustered index

Considerações sobre banco de dadosDatabase Considerations

Considere as características do banco de dados ao criar índices não clusterizados.Consider the characteristics of the database when designing nonclustered indexes.

  • Os bancos de dados ou as tabelas com baixos requisitos de atualização, mas volumes grandes de dados, podem se beneficiar de muitos índices não clusterizados para aprimorar o desempenho da consulta.Databases or tables with low update requirements, but large volumes of data can benefit from many nonclustered indexes to improve query performance. Considere a criação de índices filtrados para subconjuntos bem definidos de dados para aprimorar o desempenho da consulta, reduzir os custos de armazenamento de índice e reduzir os custos de manutenção de índice comparados a índices não clusterizados de tabela completa.Consider creating filtered indexes for well-defined subsets of data to improve query performance, reduce index storage costs, and reduce index maintenance costs compared with full-table nonclustered indexes.

    Os aplicativos do Sistema de Suporte a Decisões e os bancos de dados que contêm fundamentalmente dados somente leitura podem se beneficiar de vários índices não clusterizados.Decision Support System applications and databases that contain primarily read-only data can benefit from many nonclustered indexes. O otimizador de consulta tem mais índices dos quais selecionar para determinar o método de acesso mais rápido e as baixas características de atualização do banco de dados significam que a manutenção do índice não impedirá o desempenho.The query optimizer has more indexes to choose from to determine the fastest access method, and the low update characteristics of the database mean index maintenance will not impede performance.

  • Os aplicativos de Processamento de Transações online e os bancos de dados que contêm tabelas com grandes atualizações devem evitar a superindexação.Online Transaction Processing applications and databases that contain heavily updated tables should avoid over-indexing. Adicionalmente, os índices deveriam ser restritos, ou seja, com o mínimo possível de colunas.Additionally, indexes should be narrow, that is, with as few columns as possible.

    Números grandes de índices em uma tabela afetam o desempenho das instruções INSERT, UPDATE, DELETE e MERGE porque todos os índices precisam ser ajustados adequadamente à medida que os dados são alterados em uma tabela.Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes.

Considerações sobre consultasQuery Considerations

Antes de criar índices não clusterizados, é recomendado entender como os dados são acessados.Before you create nonclustered indexes, you should understand how your data will be accessed. Considere usar um índice não clusterizado para consultas com os seguintes atributos:Consider using a nonclustered index for queries that have the following attributes:

  • Use as cláusulas JOIN ou GROUP BY.Use JOIN or GROUP BY clauses.

    Crie vários índices não clusterizados em colunas envolvidas em operações de junção e de agrupamento e um índice clusterizado em qualquer coluna de chave estrangeira.Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.

  • Consultas que não retornam grandes conjuntos de resultados.Queries that do not return large result sets.

    Crie índices filtrados para abranger consultas que retornam um subconjunto bem definido de linhas de uma tabela grande.Create filtered indexes to cover queries that return a well-defined subset of rows from a large table.

  • Contém as colunas envolvidas frequentemente em condições de pesquisa de consulta, como a cláusula WHERE, que retorna correspondências exatas.Contain columns frequently involved in search conditions of a query, such as WHERE clause, that return exact matches.

Considerações sobre colunasColumn Considerations

Considere as colunas que tenham um ou mais destes atributos:Consider columns that have one or more of these attributes:

  • Cubra a consulta.Cover the query.

    São obtidos ganhos de desempenho quando o índice contém todas as colunas da consulta.Performance gains are achieved when the index contains all columns in the query. O otimizador de consulta pode localizar todos os valores da coluna dentro do índice. Os dados de tabela ou de índice clusterizado não são acessados, o que resulta em menos operações de E/S.The query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations. Use índice com colunas incluídas para adicionar colunas de cobertura, em vez de criar uma ampla chave de índice.Use index with included columns to add covering columns instead of creating a wide index key.

    Se a tabela tiver um índice clusterizado, a coluna ou as colunas definidas no índice clusterizado serão anexadas automaticamente ao final de cada índice não clusterizado da tabela.If the table has a clustered index, the column or columns defined in the clustered index are automatically appended to the end of each nonclustered index on the table. Isso pode produzir uma consulta coberta sem especificar as colunas de índice clusterizado na definição do índice não clusterizado.This can produce a covered query without specifying the clustered index columns in the definition of the nonclustered index. Por exemplo, se uma tabela tiver um índice clusterizado na coluna C, um índice não clusterizado nas colunas B e A , terá como colunas de valores de chave B, Ae C.For example, if a table has a clustered index on column C, a nonclustered index on columns B and A will have as its key values columns B, A, and C.

  • Muitos valores distintos, como uma combinação de sobrenome e nome, caso um índice clusterizado seja usado em outras colunas.Lots of distinct values, such as a combination of last name and first name, if a clustered index is used for other columns.

    Se houver poucos valores distintos, como apenas 1 e 0, a maioria das consultas não usará o índice porque uma verificação de tabela é, em geral, mais eficaz.If there are very few distinct values, such as only 1 and 0, most queries will not use the index because a table scan is generally more efficient. Para esse tipo de dados, considere a criação de um índice filtrado em um valor diferente que ocorra apenas em um número pequeno de linhas.For this type of data, consider creating a filtered index on a distinct value that only occurs in a small number of rows. Por exemplo, se a maioria dos valores for 0, o otimizador de consulta pode usar um índice filtrado para as linhas de dados que contêm 1.For example, if most of the values are 0, the query optimizer might use a filtered index for the data rows that contain 1.

Usar colunas incluídas para estender índices não clusterizadosUse Included Columns to Extend Nonclustered Indexes

Você pode estender a funcionalidade de índices não clusterizados acrescentando colunas de não chave ao nível folha do índice não cluster.You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. Ao incluir colunas não chave, você pode criar você índices não clusterizados que abrangem mais consultas.By including nonkey columns, you can create nonclustered indexes that cover more queries. Isto porque as colunas não chave têm os seguintes benefícios:This is because the nonkey columns have the following benefits:

  • Elas podem ser tipos de dados não permitidos como colunas de chave de índice.They can be data types not allowed as index key columns.

  • Eles não são considerados pelo Mecanismo de Banco de DadosDatabase Engine ao calcular o número de colunas de chave de índice ou o tamanho da chave de índice.They are not considered by the Mecanismo de Banco de DadosDatabase Engine when calculating the number of index key columns or index key size.

Um índice com colunas não chave incluídas pode melhorar o desempenho de consulta significativamente quando todas as colunas na consulta forem incluídas no índice como colunas de chave ou não chave.An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Os ganhos de desempenho são alcançados pois o otimizador de consulta pode localizar todos os valores de coluna dentro do índice, a tabela, ou dados de índice clusterizado não são acessados, resultando em poucas operações de E/S de disco.Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.

Observação

Quando um índice contém todas colunas referenciadas pela consulta, ele costuma ser referenciado como se abrangendo a consulta.When an index contains all the columns referenced by the query it is typically referred to as covering the query.

Enquanto as colunas de chave são armazenadas em todos os níveis do índice, as colunas não chave são armazenadas apenas em nível folha.While key columns are stored at all levels of the index, nonkey columns are stored only at the leaf level.

Usando colunas incluídas para evitar limites de tamanhoUsing Included Columns to Avoid Size Limits

Você pode incluir colunas não chave em um índice não clusterizado para evitar exceder as limitações do tamanho atual do índice, de um máximo de 16 colunas de chave, e um máximo de tamanho chave de índice de 900 bytes.You can include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. O Mecanismo de Banco de DadosDatabase Engine não considera as colunas não chave ao calcular o número de colunas de chave de índice, ou o tamanho da chave do índice.The Mecanismo de Banco de DadosDatabase Engine does not consider nonkey columns when calculating the number of index key columns or index key size.

Por exemplo, suponha que você quer indexar as colunas seguintes na tabela Document :For example, assume that you want to index the following columns in the Document table:

Title nvarchar(50)

Revision nchar(5)

FileName nvarchar(400)

Como os tipos de dados nchar e nvarchar exigem 2 bytes para cada caractere, um índice que contém essas três colunas ultrapassaria a limitação de tamanho de 900 bytes por 10 bytes (455 * 2).Because the nchar and nvarchar data types require 2 bytes for each character, an index that contains these three columns would exceed the 900 byte size limitation by 10 bytes (455 * 2). Ao usar a cláusula INCLUDE da declaração CREATE INDEX , a chave de índice pode ser definida como uma coluna não chave (Title, Revision) e FileName .By using the INCLUDE clause of the CREATE INDEX statement, the index key could be defined as (Title, Revision) and FileName defined as a nonkey column. Desse modo, o tamanho da chave de índice seria de 110 bytes (55 * 2) e o índice ainda conteria todas as colunas necessárias.In this way, the index key size would be 110 bytes (55 * 2), and the index would still contain all the required columns. A seguinte declaração cria tal índice.The following statement creates such an index.

CREATE INDEX IX_Document_Title   
ON Production.Document (Title, Revision)   
INCLUDE (FileName);   
Índice com diretrizes das colunas incluídasIndex with Included Columns Guidelines

Quando você projeta índices não clusterizados com colunas incluídas, considere as seguintes diretrizes:When you design nonclustered indexes with included columns consider the following guidelines:

  • As colunas não chave estão definidas na cláusula INCLUDE da instrução CREATE INDEX.Nonkey columns are defined in the INCLUDE clause of the CREATE INDEX statement.

  • As colunas não chave só podem ser definidas em índices não clusterizados em tabelas, ou em exibições indexadas.Nonkey columns can only be defined on nonclustered indexes on tables or indexed views.

  • São permitidos todos os tipos de dados, exceto text, ntext e image.All data types are allowed except text, ntext, and image.

  • As colunas computadas que são determinísticas e precisas ou imprecisas podem ser colunas incluídas.Computed columns that are deterministic and either precise or imprecise can be included columns. Para obter mais informações, consulte Indexes on Computed Columns.For more information, see Indexes on Computed Columns.

  • Assim como com as colunas de chave, as colunas computadas derivadas dos tipos de dados image, ntext, e text podem ser colunas não chave (incluídas), desde que o tipo de dados da coluna computada seja permitido como uma coluna de índice não chave.As with key columns, computed columns derived from image, ntext, and text data types can be nonkey (included) columns as long as the computed column data type is allowed as a nonkey index column.

  • Os nomes das colunas não podem ser especificados na lista INCLUDE e na lista de coluna de chave.Column names cannot be specified in both the INCLUDE list and in the key column list.

  • Os nomes das colunas não podem ser repetidos na lista INCLUDE.Column names cannot be repeated in the INCLUDE list.

Diretrizes do tamanho da colunaColumn Size Guidelines
  • Pelo menos uma coluna de chave deve ser definida.At least one key column must be defined. O número de máximo de colunas não chave é de 1023 colunas.The maximum number of nonkey columns is 1023 columns. Esse é o número máximo de colunas de tabela menos 1.This is the maximum number of table columns minus 1.

  • As colunas de chave de índice, exceto as não chave, devem seguir as restrições de tamanho de índice de no máximo 16 colunas de chave, e um tamanho total de chave de índice de no máximo 900 bytes.Index key columns, excluding nonkeys, must follow the existing index size restrictions of 16 key columns maximum, and a total index key size of 900 bytes.

  • O tamanho total de todas as colunas não chave está limitado somente pelo tamanho especificado das colunas na cláusula INCLUDE; por exemplo, as colunas varchar(max) estão limitadas a 2 GB.The total size of all nonkey columns is limited only by the size of the columns specified in the INCLUDE clause; for example, varchar(max) columns are limited to 2 GB.

Diretrizes para modificação de colunaColumn Modification Guidelines

Quando você modifica uma coluna de tabela que estava definida como uma coluna incluída, as restrições seguintes se aplicam:When you modify a table column that has been defined as an included column, the following restrictions apply:

  • As colunas não chave não podem ser soltar das tabelas, a menos que o índice seja solto antes.Nonkey columns cannot be dropped from the table unless the index is dropped first.

  • As colunas não chave não podem ser alteradas, exceto para fazerem o seguinte:Nonkey columns cannot be changed, except to do the following:

    • Alterar a nulidade da coluna da coluna NOT NULL até NULL.Change the nullability of the column from NOT NULL to NULL.

    • Aumentar o comprimento das colunas varchar, nvarchar, ou varbinary .Increase the length of varchar, nvarchar, or varbinary columns.

      Observação

      Estas restrições de modificação de coluna também se aplicam para indexar colunas de chave.These column modification restrictions also apply to index key columns.

Recomendações de designDesign Recommendations

Redesenhe índices não clusterizados com um comprimento de chave de índice, de tal forma que apenas as colunas usadas para buscas e pesquisas sejam colunas de chave.Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Faça todas as outras colunas que abrangem a consulta colunas não chave incluídas.Make all other columns that cover the query included nonkey columns. Deste modo, você terá todas as colunas necessárias para abranger a consulta, mas a chave de índice em si é pequena e eficiente.In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.

Por exemplo, suponha que você quer projetar um índice para abranger a consulta seguinte.For example, assume that you want to design an index to cover the following query.

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode  
FROM Person.Address  
WHERE PostalCode BETWEEN N'98000' and N'99999';  

Para abranger a consulta, cada coluna deve ser definida no índice.To cover the query, each column must be defined in the index. Embora você possa definir todas as colunas como colunas de chave, o tamanho chave seria de 334 bytes.Although you could define all columns as key columns, the key size would be 334 bytes. Em razão da única coluna de fato usada como critério de pesquisa ser a coluna PostalCode , que tem um comprimento de 30 bytes, um melhor design de índice definiria PostalCode como sendo a coluna de chave e incluiria todas as outras colunas como colunas que não são colunas de chave.Because the only column actually used as search criteria is the PostalCode column, having a length of 30 bytes, a better index design would define PostalCode as the key column and include all other columns as nonkey columns.

A seguinte declaração cria um índice com colunas incluídas para abranger a consulta.The following statement creates an index with included columns to cover the query.

CREATE INDEX IX_Address_PostalCode  
ON Person.Address (PostalCode)  
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);  
Considerações sobre desempenhoPerformance Considerations

Evite a adição desnecessária de colunas.Avoid adding unnecessary columns. Adicionar muitas colunas de índice, sejam elas chave ou não, pode gerar as seguintes implicações no desempenho:Adding too many index columns, key or nonkey, can have the following performance implications:

  • Poucas filas de índice se ajustarão em uma página.Fewer index rows will fit on a page. Isto poderia criar aumentos de E/S e eficiência de cache reduzida.This could create I/O increases and reduced cache efficiency.

  • Será necessário mais espaço em disco para armazenar o índice.More disk space will be required to store the index. Em particular, acrescentar os tipos de dados varchar(max), nvarchar(max), varbinary(max)ou xml como colunas de índice não chave pode aumentar significativamente os requisitos de espaço em disco.In particular, adding varchar(max), nvarchar(max), varbinary(max), or xml data types as nonkey index columns may significantly increase disk space requirements. Isto porque os valores de coluna são copiados no nível folha de índice.This is because the column values are copied into the index leaf level. Portanto, eles residem no índice e na tabela base.Therefore, they reside in both the index and the base table.

  • A manutenção do índice pode aumentar o tempo necessário para executar modificações, inserções, atualizações ou exclusões, para a tabela subjacente ou exibição indexada.Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.

Você terá que determinar se os ganhos no desempenho de consulta superam o efeito no desempenho durante a modificação de dados, e em requisitos adicionais de espaço em disco.You will have to determine whether the gains in query performance outweigh the affect to performance during data modification and in additional disk space requirements.

Ícone de seta usado com o link voltar ao início neste guiaArrow icon used with Back to Top link In This Guide

Diretrizes de design de índice exclusivoUnique Index Design Guidelines

Um índice exclusivo garante que a chave de índice não contém nenhum valor duplicado e, portanto, cada linha na tabela é exclusiva de algum modo.A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Especificar um índice exclusivo só faz sentido quando a exclusividade for uma característica dos próprios dados.Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. Por exemplo, se você quiser garantir que os valores na coluna NationalIDNumber na tabela HumanResources.Employee sejam exclusivos, quando a chave primária for EmployeeID, crie uma restrição UNIQUE na coluna NationalIDNumber .For example, if you want to make sure that the values in the NationalIDNumber column in the HumanResources.Employee table are unique, when the primary key is EmployeeID, create a UNIQUE constraint on the NationalIDNumber column. Se o usuário tentar digitar o mesmo valor naquela coluna para mais de um empregado, será exibida uma mensagem de erro e o valor duplicado não é inserido.If the user tries to enter the same value in that column for more than one employee, an error message is displayed and the duplicate value is not entered.

Com índices exclusivos de multicolunas, o índice garante que cada combinação de valores na chave de índice é exclusivo.With multicolumn unique indexes, the index guarantees that each combination of values in the index key is unique. Por exemplo, se um índice exclusivo for criado em uma combinação de colunas LastName, FirstNamee MiddleName , duas linhas na tabela não poderão ter a mesma combinação de valores que essas colunas.For example, if a unique index is created on a combination of LastName, FirstName, and MiddleName columns, no two rows in the table could have the same combination of values for these columns.

Tanto os índices clusterizados quanto os não clusterizados podem ser exclusivos.Both clustered and nonclustered indexes can be unique. Contanto que os dados na coluna sejam exclusivos, você pode criar um índice clusterizado exclusivo e não clusterizado na mesma tabela.Provided that the data in the column is unique, you can create both a unique clustered index and multiple unique nonclustered indexes on the same table.

Os benefícios dos índices exclusivos incluem o seguinte:The benefits of unique indexes include the following:

  • A integridade de dados das colunas definidas é garantida.Data integrity of the defined columns is ensured.

  • São fornecidas informações úteis adicionais ao otimizador de consultas.Additional information helpful to the query optimizer is provided.

Criar uma restrição PRIMARY KEY ou UNIQUE automaticamente gera um índice exclusivo nas colunas especificadas.Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns. Não há nenhuma diferença significativa entre criar uma restrição UNIQUE e criar um índice exclusivo independente de uma restrição.There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. A validação de dados ocorre da mesma maneira e o otimizador de consultas não diferencia entre um índice exclusivo criado por uma restrição ou manualmente.Data validation occurs in the same manner and the query optimizer does not differentiate between a unique index created by a constraint or manually created. Entretanto, você deverá criar uma restrição UNIQUE ou PRIMARY KEY na coluna quando o objetivo for a integridade de dados.However, you should create a UNIQUE or PRIMARY KEY constraint on the column when data integrity is the objective. Fazendo isso o objetivo do índice será claro.By doing this the objective of the index will be clear.

ConsideraçõesConsiderations

  • Um índice exclusivo, uma restrição UNIQUE ou uma restrição PRIMARY KEY não poderão ser criados, se existirem valores de chave duplicados nos dados.A unique index, UNIQUE constraint, or PRIMARY KEY constraint cannot be created if duplicate key values exist in the data.

  • Se os dados forem exclusivos e você quiser impor exclusividade, criar um índice exclusivo em vez de um índice não exclusivo, na mesma combinação de colunas, fornecerá informações adicionais para otimizador de consultas que poderá produzir planos de execução mais eficientes.If the data is unique and you want uniqueness enforced, creating a unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that can produce more efficient execution plans. Criar um índice exclusivo (preferivelmente criando uma restrição UNIQUE) é recomendável nesse caso.Creating a unique index (preferably by creating a UNIQUE constraint) is recommended in this case.

  • Um índice não clusterizado exclusivo pode conter colunas não chave incluídas.A unique nonclustered index can contain included nonkey columns. Para obter mais informações, consulte Índice com colunas incluídas.For more information, see Index with Included Columns.

Ícone de seta usado com o link voltar ao início neste guiaArrow icon used with Back to Top link In This Guide

Diretrizes de criação de índice filtradoFiltered Index Design Guidelines

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 e reduzir os custos de manutenção e armazenamento do índice em comparação com os índices de tabela completa.A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

Aplica-se a: do SQL Server 2008SQL Server 2008 ao SQL Server 2014SQL Server 2014.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2014SQL Server 2014.

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 afetados.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 affected. É possível ter um grande número de índices filtrados, especialmente quando eles contêm dados que são raramente afetados.It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. Do mesmo modo, se um índice filtrado tiver apenas dados afetados com frequência, seu tamanho reduzido diminuirá o custo de atualização das estatísticas.Similarly, if a filtered index contains only the frequently affected 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.

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.Filtered indexes are useful when columns contain well-defined subsets of data that queries reference in SELECT statements. São exemplos:Examples are:

  • Colunas esparsas que contêm apenas alguns valores não NULL.Sparse columns that contain only a few non-NULL values.

  • Colunas heterogêneas que contêm categorias de dados.Heterogeneous columns that contain categories of data.

  • Colunas que contêm intervalos de valores como quantias em dinheiro, hora e datas.Columns that contain ranges of values such as dollar amounts, time, and dates.

  • Partições de tabela definidas pela lógica de comparação simples para obter valores de coluna.Table partitions that are defined by simple comparison logic for column values.

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.Reduced maintenance costs for filtered indexes are most noticeable when the number of rows in the index is small compared with a full-table index. 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.If the filtered index includes most of the rows in the table, it could cost more to maintain than a full-table index. Nesse caso, você deve usar um índice de tabela completa em vez do índice filtrado.In this case, you should use a full-table index instead of a filtered index.

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.

Considerações de criaçãoDesign Considerations

Para criar índices filtrados eficazes, é importante entender quais consultas o aplicativo usa e como elas se relacionam com os subconjuntos de dados.In order to design effective filtered indexes, it is important to understand what queries your application uses and how they relate to subsets of your data. Alguns exemplos de dados com subconjuntos bem-definidos são as colunas com valores predominantemente NULL, as colunas com categorias de valores heterogêneas e as colunas com intervalos de valores diferentes.Some examples of data that have well-defined subsets are columns with mostly NULL values, columns with heterogeneous categories of values and columns with distinct ranges of values. As considerações sobre criação a seguir apresentam uma variedade de cenários em que um índice filtrado pode ser vantajoso com relação aos índices de tabela completa.The following design considerations give a variety of scenarios for when a filtered index can provide advantages over full-table indexes.

Índices filtrados para subconjuntos de dadosFiltered Indexes for Subsets of Data

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.

Por exemplo, o banco de dados AdventureWorks2012 tem uma tabela Production.BillOfMaterials com 2.679 linhas.For example, the AdventureWorks2012 database has a Production.BillOfMaterials table with 2679 rows. A coluna EndDate tem apenas 199 linhas que contêm um valor não NULL e outras 2.480 linhas que contêm NULL.The EndDate column has only 199 rows that contain a non-NULL value and the other 2480 rows contain 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.The following filtered index would cover queries that return the columns defined in the index and that select only rows with a non-NULL value for EndDate.

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.The filtered index FIBillOfMaterialsWithEndDate 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.

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

Para obter mais informações sobre como criar índices filtrados e definir a expressão de predicado do índice filtrado, consulte Create Filtered Indexes.For more information about how to create filtered indexes and how to define the filtered index predicate expression, see Create Filtered Indexes.

Índices filtrados para dados heterogêneosFiltered Indexes for Heterogeneous Data

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.

Por exemplo, a cada produto listado na tabela Production.Product é atribuído um ProductSubcategoryIDque, por sua vez, está associado às categorias de produtos Bikes, Components, Clothing ou Accessories.For example, the products listed in the Production.Product table are each assigned to a ProductSubcategoryID, which are in turn associated with the product categories Bikes, Components, Clothing, or Accessories. Essas categorias são heterogêneas porque os valores das coluna da tabela Production.Product não estão estreitamente correlacionados.These categories are heterogeneous because their column values in the Production.Product table are not closely correlated. Por exemplo, as colunas Color, ReorderPoint, ListPrice, Weight, Classe Style têm características exclusivas para cada categoria de produto.For example, the columns Color, ReorderPoint, ListPrice, Weight, Class, and Style have unique characteristics for each product category. Suponha que haja consultas frequentes sobre acessórios que possuem subcategorias entre 27 e 36.Suppose that there are frequent queries for accessories which have subcategories between 27 and 36 inclusive. É possível aprimorar o desempenho das consultas sobre acessórios criando um índice filtrado nas subcategorias de acessórios, conforme ilustrado no exemplo a seguir.You can improve the performance of queries for accessories by creating a filtered index on the accessories subcategories as shown in the following example.

CREATE NONCLUSTERED INDEX FIProductAccessories  
    ON Production.Product (ProductSubcategoryID, ListPrice)   
        Include (Name)  
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;

O índice filtrado FIProductAccessories abrange a seguinte consulta porque os resultados da consultaThe filtered index FIProductAccessories covers the following query because the query

estão contidos no índice e o plano da consulta não inclui uma pesquisa de tabela base.results are contained in the index and the query plan does not include a base table lookup. 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.For example, the query predicate expression ProductSubcategoryID = 33 is a subset of the filtered index predicate ProductSubcategoryID >= 27 and ProductSubcategoryID <= 36, the ProductSubcategoryID and ListPrice columns in the query predicate are both key columns in the index, and name is stored in the leaf level of the index as an included column.

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

Colunas de ChaveKey Columns

Uma prática recomendada é incluir um pequeno número de colunas de chave ou incluídas em uma definição de índice filtrado e inserir apenas as colunas necessárias para o otimizador de consulta escolher o índice filtrado para o plano de execução da consulta.It is a best practice to include a small number of key or included columns in a filtered index definition, and to incorporate only the columns that are necessary for the query optimizer to choose the filtered index for the query execution plan. O otimizador de consulta pode escolher um índice filtrado para a consulta, independentemente de ele abranger ou não a consulta.The query optimizer can choose a filtered index for the query regardless of whether it does or does not cover the query. No entanto, é mais provável que o otimizador de consulta escolha um índice filtrado se ele abranger a consulta.However, the query optimizer is more likely to choose a filtered index if it covers the query.

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.In some cases, a filtered index covers the query without including the columns in the filtered index expression as key or included columns in the filtered index definition. 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.The following guidelines explain when a column in the filtered index expression should be a key or included column in the filtered index definition. Os exemplos se referem ao índice filtrado FIBillOfMaterialsWithEndDate que foi criado anteriormente.The examples refer to the filtered index, FIBillOfMaterialsWithEndDate that was created previously.

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. Por exemplo, FIBillOfMaterialsWithEndDate abrange a consulta a seguir porque o predicado da consulta é equivalente à expressão de filtro e EndDate não é retornado com os resultados da consulta.For example, FIBillOfMaterialsWithEndDate covers the following query because the query predicate is equivalent to the filter expression, and EndDate is not returned with the query results. FIBillOfMaterialsWithEndDate não precisa de EndDate como uma coluna de chave ou incluída na definição do índice filtrado.FIBillOfMaterialsWithEndDate does not need EndDate as a key or included column in the filtered index definition.

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

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. Por exemplo, FIBillOfMaterialsWithEndDate é válido para a consulta a seguir, porque seleciona um subconjunto de linhas do índice filtrado.For example, FIBillOfMaterialsWithEndDate is valid for the following query because it selects a subset of rows from the filtered index. Contudo, não abrange a consulta a seguir porque EndDate é usado na comparação de EndDate > '20040101', que não é equivalente à expressão do índice filtrado.However, it does not cover the following query because EndDate is used in the comparison EndDate > '20040101', which is not equivalent to the filtered index expression. O processador de consultas não pode executar essa consulta sem observar os valores de EndDate.The query processor cannot execute this query without looking up the values of EndDate. Portanto, EndDate deve ser uma coluna de chave ou incluída na definição do índice filtrado.Therefore, EndDate should be a key or included column in the filtered index definition.

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

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. Por exemplo, FIBillOfMaterialsWithEndDate não abrange a consulta a seguir, porque retorna a coluna EndDate nos resultados da consulta.For example, FIBillOfMaterialsWithEndDate does not cover the following query because it returns the EndDate column in the query results. Portanto, EndDate deve ser uma coluna de chave ou incluída na definição do índice filtrado.Therefore, EndDate should be a key or included column in the filtered index definition.

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

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.

Operadores de conversão de dados no predicado do filtroData Conversion Operators in the Filter Predicate

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.

O exemplo a seguir cria uma tabela com diversos tipos de dados.The following example creates a table with a variety of data types.

USE AdventureWorks2012;  
GO  
CREATE TABLE dbo.TestTable (a int, b varbinary(4));  

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.In the following filtered index definition, column b is implicitly converted to an integer data type for the purpose of comparing it to the constant 1. Isso gera a mensagem de erro 10611 porque a conversão ocorre à esquerda do operador no predicado filtrado.This generates error message 10611 because the conversion occurs on the left hand side of the operator in the filtered predicate.

CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)  
WHERE b = 1;  

A solução é converter a constante à direita para o mesmo tipo da coluna b, como mostra o seguinte exemplo:The solution is to convert the constant on the right hand side to be of the same type as column b, as seen in the following example:

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

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.Moving the data conversion from the left side to the right side of a comparison operator might change the meaning of the conversion. 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.In the above example, when the CONVERT operator was added to the right side, the comparison changed from an integer comparison to a varbinary comparison.

Ícone de seta usado com o link voltar ao início neste guiaArrow icon used with Back to Top link In This Guide

Leitura adicionalAdditional Reading

Melhorando o desempenho com exibições indexadas do SQL Server 2008Improving Performance with SQL Server 2008 Indexed Views

Partitioned Tables and IndexesPartitioned Tables and Indexes