Guia de arquitetura e design de índices do SQL ServerSQL Server Index Architecture and Design Guide

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure simSQL Data Warehouse do Azure simParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

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 design de índices do SQL ServerSQL Server contém informações sobre a arquitetura de índices e as melhores práticas para ajudá-lo a criar índices efetivos de acordo com as necessidades de seu aplicativo.This SQL ServerSQL Server index design guide contains information on index architecture, and best practices to help you design effective indexes to meet the needs of your application.

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.

Este guia aborda os seguintes tipos de índices:This guide covers the following types of indexes:

  • ClusterizadoClustered
  • Não clusterizadoNonclustered
  • ExclusivoUnique
  • FiltradoFiltered
  • columnstoreColumnstore
  • HashHash
  • Não clusterizado com otimização de memóriaMemory-Optimized Nonclustered

Para obter informações sobre índices XML, consulte Visão geral de índices XML.For information about XML indexes, see XML Indexes Overview.

Para obter informações sobre índices espaciais, consulte Visão geral de índices espaciais.For information about Spatial indexes, see Spatial Indexes Overview.

Para obter informações sobre índices de texto completo, consulte Popular índices de texto completo.For information about Full-text indexes, see Populate Full-Text Indexes.

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

Um índice é uma estrutura em disco ou in-memory associada a uma tabela ou exibição que acelera a recuperação de linhas de uma tabela ou exibição.An index is an on-disk or in-memory 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. Para índices em disco, essas chaves são armazenadas em uma estrutura (árvore B) que permite ao SQL Server localizar a linha ou as linhas associadas aos valores de chave de forma rápida e eficaz.For on-disk indexes, 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.

Um índice armazena dados logicamente organizados como uma tabela com linhas e colunas e fisicamente armazenados em um formato de dados de linha chamado rowstore 1 ou armazenados em um formato de dados de coluna chamado columnstore .An index stores data logically organized as a table with rows and columns, and physically stored in a row-wise data format called rowstore 1, or stored in a column-wise data format called columnstore.

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.

1 Rowstore tem sido o modo tradicional de armazenar dados de tabela relacional.1 Rowstore has been the traditional way to store relational table data. No SQL ServerSQL Server, rowstore refere-se à tabela em que o formato de armazenamento de dados subjacente é um heap, uma árvore B (índice clusterizado) ou uma tabela com otimização de memória.In SQL ServerSQL Server, rowstore refers to table where the underlying data storage format is a heap, a B-tree (clustered index), or a memory-optimized table.

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.

  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 beneficiará 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, todos os índices que contêm essa coluna deverão ser atualizados, 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. Essas são as colunas SARGable1.These are your SARGable1 columns. 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.

    Importante

    Índices abrangentes são a designação para um índice não clusterizado que resolve um ou vários resultados de consulta semelhantes diretamente, sem acesso à tabela base e sem incorrer em pesquisas.Covering indexes are the designation for a nonclustered index that resolves one or several similar query results directly with no access to its base table, and without incurring in lookups. Esses índices têm todas as colunas não SARGable necessárias em seu nível de folha.Such indexes have all the necessary non-SARGable columns in its leaf level. Isso significa que as colunas retornadas por qualquer cláusula SELECT e todos os argumentos WHERE e JOIN são cobertos pelo índice.This means that the columns returned by either the SELECT clause and all the WHERE and JOIN arguments are covered by the index. Haverá potencialmente muito menos E/S para executar a consulta, se o índice for estreito o suficiente em comparação com as linhas e colunas da tabela em si, o que significa que ele é um subconjunto real do total de colunas.There is potentially much less I/O to execute the query, if the index is narrow enough when compared to the rows and columns in the table itself, meaning it is a real sub-set of the total columns. Considere cobrir índices ao selecionar uma pequena parte de uma tabela grande e quando essa pequena parte for definida por um predicado fixo, como colunas esparsas que contêm apenas alguns valores não NULL, por exemplo.Consider covering indexes when selecting a small portion of a large table, and where that small portion is defined by a fixed predicate, such as sparse columns that contain only a few non-NULL values, for example.

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

1 o termo SARGable em bancos de dados relacionais se refere a um predicado Search ARGument-able que pode aproveitar um índice para acelerar a execução da consulta.1 The term SARGable in relational databases refers to a Search ARGument-able predicate that can leverage an index to speed up the execution of the query.

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.

  • Colunas que são dos tipos 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 de um índice não clusterizado, 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, consulte Í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
  • Columnstore versus rowstoreColumnstore versus rowstore
  • Hash versus não clusterizado para tabelas com otimização de memóriaHash versus nonclustered for Memory-Optimized tables

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.

IndexSort1

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.

InsertSort2

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.

MetadadosMetadata

Use essas exibições de metadados para ver os atributos de índices.Use these metadata views to see attributes of indexes. Mais informações arquitetônicas são inseridas em algumas dessas exibições.More architectural information is embedded in some of these views.

Observação

Em índices columnstore, todas as colunas são armazenadas nos metadados como colunas incluídas.For columnstore indexes, all columns are stored in the metadata as included columns. O índice columnstore não tem colunas de chave.The columnstore index does not have key columns.

sys.indexes (Transact-SQL)sys.indexes (Transact-SQL) sys.index_columns (Transact-SQL)sys.index_columns (Transact-SQL)
sys.partitions (Transact-SQL)sys.partitions (Transact-SQL) sys.internal_partitions (Transact-SQL)sys.internal_partitions (Transact-SQL)
sys.dm_db_index_operational_stats (Transact-SQL)sys.dm_db_index_operational_stats (Transact-SQL) sys.dm_db_index_physical_stats (Transact-SQL)sys.dm_db_index_physical_stats (Transact-SQL)
sys.column_store_segments (Transact-SQL)sys.column_store_segments (Transact-SQL) sys.column_store_dictionaries (Transact-SQL)sys.column_store_dictionaries (Transact-SQL)
sys.column_store_row_groups (Transact-SQL)sys.column_store_row_groups (Transact-SQL) sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)
sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) sys.dm_column_store_object_pool (Transact-SQL)sys.dm_column_store_object_pool (Transact-SQL)
sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)sys.dm_db_column_store_row_group_operational_stats (Transact-SQL) sys.dm_db_xtp_hash_index_stats (Transact-SQL)sys.dm_db_xtp_hash_index_stats (Transact-SQL)
sys.dm_db_xtp_index_stats (Transact-SQL)sys.dm_db_xtp_index_stats (Transact-SQL) sys.dm_db_xtp_object_stats (Transact-SQL)sys.dm_db_xtp_object_stats (Transact-SQL)
sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL) sys.dm_db_xtp_table_memory_stats (Transact-SQL)sys.dm_db_xtp_table_memory_stats (Transact-SQL)
sys.hash_indexes (Transact-SQL)sys.hash_indexes (Transact-SQL) sys.memory_optimized_tables_internal_attributes (Transact-SQL)sys.memory_optimized_tables_internal_attributes (Transact-SQL)

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 adicionará automaticamente uma coluna de indicador de exclusividade de 4 bytes à 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 partição 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. Ele também terá uma unidade de alocação ROW_OVERFLOW_DATA por partição se tiver colunas de comprimento variável que excedem 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.

bokind2

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.

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

  • Usam 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 uma restrição PRIMARY KEY na coluna EmployeeID melhorará 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.

    Dica

    Se não for especificado de forma diferente, ao criar uma restrição PRIMARY KEY, SQL ServerSQL Server criará um índice clusterizado para dar suporte a essa restrição.If not specified differently, when creating a PRIMARY KEY constraint, SQL ServerSQL Server creates a clustered index to support that constraint. Embora um uniqueidentifier possa ser usado para impor exclusividade como uma PRIMARY KEY, ele não é uma chave de clustering eficiente.Although a uniqueidentifier can be used to enforce uniqueness as a PRIMARY KEY, it is not an efficient clustering key. Se estiver usando um uniqueidentifier como a PRIMARY KEY, a recomendação é criá-lo como um índice não clusterizado e usar outra coluna, como um IDENTITY para criar o índice clusterizado.If using a uniqueidentifier as PRIMARY KEY, the recommendation is to create it as a nonclustered index, and use another column such as an IDENTITY to create the clustered index.

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

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.

  • O nível de folha de um índice não clusterizado é constituído de páginas de índice, em vez de páginas de dados.The leaf level 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, ele 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.

bokind1a

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 (OLTP) e os bancos de dados que contêm tabelas com grandes atualizações devem evitar a superindexação.Online Transaction Processing (OLTP) 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:

  • Usam 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.

    Dica

    Normalmente, a cláusula WHERE da instrução CREATE INDEX corresponde à cláusula WHERE de uma consulta que está sendo abordada.Typically the WHERE clause of the CREATE INDEX statement matches the WHERE clause of a query being covered.

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

    Dica

    Considere o custo versus benefício ao adicionar novos índices.Consider the cost versus benefit when adding new indexes. Pode ser preferível para consolidar as necessidades de consulta adicionais em um índice existente.It may be preferable to consolidate additional query needs into an existing index. Por exemplo, considere a adição de uma ou duas colunas extras de nível de folha a um índice existente, se isso permitir a cobertura de várias consultas críticas, em vez de ter uma cobertura exata de índice por consulta crítica.For example, consider adding one or two extra leaf level columns to an existing index, if it allows coverage of several critical queries, instead of having one exactly covering index per each critical query.

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, ntexte 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, ntexte 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.

    • Aumente o tamanho das colunas varchar, nvarcharou 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.

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.

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 2017SQL Server 2017.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

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.

Dica

A definição do índice columnstore não clusterizado dá suporte ao uso de uma condição filtrada.The nonclustered columnstore index definition supports using a filtered condition. Para minimizar o impacto no desempenho da adição de um índice columnstore em uma tabela OLTP, use uma condição filtrada para criar um índice columnstore não clusterizado apenas nos dados inativos da sua carga de trabalho operacional.To minimize the performance impact of adding a columnstore index on an OLTP table, use a filtered condition to create a nonclustered columnstore index on only the cold data of your operational workload.

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

Diretrizes de design de índice columnstoreColumnstore Index Design Guidelines

Um columnstore index é uma tecnologia para armazenamento, recuperação e gerenciamento de dados usando um formato de dados colunar, chamado columnstore.A columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore. Para obter mais informações, consulte Visão geral de índices columnstore.For more information, refer to Columnstore Indexes overview.

Para obter informações de versão, veja Índices Columnstore – Novidades.For version information, see Columnstore indexes - What's new.

Arquitetura de índices columnstoreColumnstore Index Architecture

Com estas noções básicas ficará mais fácil entender outros artigos sobre columnstore que explicam como usá-los com eficiência.Knowing these basics will make it easier to understand other columnstore articles that explain how to use them effectively.

O armazenamento de dados usa compactação de columnstore e rowstoreData storage uses columnstore and rowstore compression

Em discussões sobre índices columnstore, usamos os termos rowstore e columnstore para enfatizar o formato do armazenamento de dados.When discussing columnstore indexes, we use the terms rowstore and columnstore to emphasize the format for the data storage. Os índices columnstore usam os dois tipos de armazenamento.Columnstore indexes use both types of storage.

Clustered Columnstore IndexClustered Columnstore Index

  • Um columnstore são dados organizados logicamente como uma tabela com linhas e colunas e armazenados fisicamente em um formato de dados com reconhecimento de colunas.A columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format.

    Um índice columnstore armazena fisicamente a maioria dos dados no formato columnstore.A columnstore index physically stores most of the data in columnstore format. No formato columnstore, os dados são compactados e descompactados como colunas.In columnstore format, the data is compressed and uncompressed as columns. Não é necessário descompactar outros valores em cada linha que não sejam solicitados pela consulta.There is no need to uncompress other values in each row that are not requested by the query. Isso acelera a verificação de uma coluna inteira de uma tabela grande.This makes it fast to scan an entire column of a large table.

  • Um rowstore são dados logicamente organizados como uma tabela com linhas e colunas e fisicamente armazenados em um formato de dados com reconhecimento de linha.A rowstore is data that is logically organized as a table with rows and columns, and then physically stored in a row-wise data format. Essa tem sido a maneira tradicional de armazenar dados de tabelas relacionais, como um índice de heap ou árvore B clusterizado.This has been the traditional way to store relational table data such as a heap or clustered B-tree index.

    Um índice columnstore também armazena fisicamente algumas linhas em um formato de rowstore, chamado deltastore.A columnstore index also physically stores some rows in a rowstore format called a deltastore. O deltastore, também chamado de rowgroups delta, é um espaço reservado para linhas que são muito poucas para se qualificarem para compactação no columnstore.The deltastore,also called delta rowgroups, is a holding place for rows that are too few in number to qualify for compression into the columnstore. Cada rowgroup delta é implementado como um índice de árvore B clusterizado.Each delta rowgroup is implemented as a clustered B-tree index.

  • O deltastore é um local de espera para linhas que são muito poucas para serem compactadas no columnstore.The deltastore is a holding place for rows that are too few in number to be compressed into the columnstore. O deltastore armazena as linhas no formato rowstore.The deltastore stores the rows in rowstore format.

Operações são executadas em rowgroups e em segmentos de colunasOperations are performed on rowgroups and column segments

O índice columnstore agrupa linhas em unidades gerenciáveis.The columnstore index groups rows into manageable units. Cada uma dessas unidades é chamada de um rowgroup.Each of these units is called a rowgroup. Para melhor desempenho, o número de linhas em um rowgroup é grande o suficiente para melhorar as taxas de compactação e pequeno o suficiente para beneficiar-se de operações na memória.For best performance, the number of rows in a rowgroup is large enough to improve compression rates and small enough to benefit from in-memory operations.

Por exemplo, o índice columnstore executa estas operações em rowgroups:For example, the columnstore index performs these operations on rowgroups:

  • Compacta rowgroups no columnstore.Compresses rowgroups into the columnstore. A compactação é executada em cada segmento de coluna dentro de um rowgroup.Compression is performed on each column segment within a rowgroup.
  • Mescla rowgroups durante uma operação ALTER INDEX ... REORGANIZE.Merges rowgroups during an ALTER INDEX ... REORGANIZE operation.
  • Cria novos rowgroups durante uma operação ALTER INDEX ... REBUILD.Creates new rowgroups during an ALTER INDEX ... REBUILD operation.
  • Relata a integridade e a fragmentação do rowgroup nas DMVs (exibições de gerenciamento dinâmico).Reports on rowgroup health and fragmentation in the dynamic management views (DMVs).

O deltastore é composto de um ou mais rowgroups chamados rowgroups delta.The deltastore is comprised of one or more rowgroups called delta rowgroups. Cada rowgroup delta é um índice de árvore B clusterizado que armazena pequenas cargas e inserções em massa até que o rowgroup contenha 1.048.576 linhas ou até que o índice seja recompilado.Each delta rowgroup is a clustered B-tree index that stores small bulk loads and inserts until the rowgroup contains 1,048,576 rows, or until the index is rebuilt. Quando um rowgroup delta contém 1.048.576 linhas, ele é marcado como fechado e espera por um processo chamado motor de tupla para compactá-lo no columnstore.When a delta rowgroup contains 1,048,576 rows it is marked as closed, and waits for a process called the tuple-mover to compress it into the columnstore.

Cada coluna tem alguns de seus valores em cada rowgroup.Each column has some of its values in each rowgroup. Esses valores são chamados de segmentos de coluna.These values are called column segments. Cada rowgroup contém um segmento de coluna para cada coluna na tabela.Each rowgroup contains one column segment for every column in the table. Cada coluna tem um segmento de coluna em cada rowgroup.Each column has one column segment in each rowgroup.

Column segmentColumn segment

Quando o índice columnstore compacta um rowgroup, ele compacta cada segmento de coluna separadamente.When the columnstore index compresses a rowgroup, it compresses each column segment separately. Para descompactar uma coluna inteira, o índice columnstore só precisa descompactar um segmento de coluna de cada rowgroup.To uncompress an entire column, the columnstore index only needs to uncompress one column segment from each rowgroup.

Cargas e inserções pequenas vão para o deltastoreSmall loads and inserts go to the deltastore

Um índice columnstore melhora a compactação e o desempenho do columnstore por compactar pelo menos 102.400 linhas por vez no índice columnstore.A columnstore index improves columnstore compression and performance by compressing at least 102,400 rows at a time into the columnstore index. Para compactar linhas em massa, o índice columnstore acumula pequenas cargas e insere no deltastore.To compress rows in bulk, the columnstore index accumulates small loads and inserts in the deltastore. As operações de deltastore são tratadas em segundo plano.The deltastore operations are handled behind the scenes. Para retornar os resultados corretos da consulta, o índice columnstore clusterizado combina os resultados da consulta de columnstore e deltastore.To return the correct query results, the clustered columnstore index combines query results from both the columnstore and the deltastore.

As linhas vão para o deltastore quando:Rows go to the deltastore when they are:

  • São inseridas com a instrução INSERT INTO ... VALUES.Inserted with the INSERT INTO ... VALUES statement.
  • Estão no final de uma carga em massa e em número menor de 102.400.At the end of a bulk load and they number less than 102,400.
  • São atualizadas.Updated. Cada atualização é implementada como uma exclusão e uma inserção.Each update is implemented as a delete and an insert.

O deltastore também armazena uma lista de IDs de linhas excluídas que foram marcadas como excluídas, mas ainda não foram excluídas fisicamente do columnstore.The deltastore also stores a list of IDs for deleted rows that have been marked as deleted but not yet physically deleted from the columnstore.

Quando os rowgroups delta estão cheios, eles são compactados para o columnstoreWhen delta rowgroups are full they get compressed into the columnstore

Os índices columnstore clusterizados coletam até 1.048.576 linhas em cada rowgroup delta antes de compactar o rowgroup no columnstore.Clustered columnstore indexes collect up to 1,048,576 rows in each delta rowgroup before compressing the rowgroup into the columnstore. Isso melhora a compactação do índice columnstore.This improves the compression of the columnstore index. Quando um rowgroup delta contém 1.048.576 linhas, o índice columnstore marca o rowgroup como fechado.When a delta rowgroup contains 1,048,576 rows, the columnstore index marks the rowgroup as closed. Um processo em segundo plano, chamado motor de tupla, localiza cada rowgroup fechado e compacta-o no columnstore.A background process, called the tuple-mover, finds each closed rowgroup and compresses it into the columnstore.

Você pode forçar rowgroups delta para o columnstore usando ALTER INDEX para reccompilar ou reorganizar o índice.You can force delta rowgroups into the columnstore by using ALTER INDEX to rebuild or reorganize the index. Observe que, se houver pressão de memória durante a compactação, o índice columnstore poderá reduzir o número de linhas no rowgroup compactado.Note that if there is memory pressure during compression, the columnstore index might reduce the number of rows in the compressed rowgroup.

Cada partição de tabela tem seus próprios rowgroups e rowgroups deltaEach table partition has its own rowgroups and delta rowgroups

O conceito de particionamento é o mesmo em um índice clusterizado, um heap e um índice columnstore.The concept of partitioning is the same in both a clustered index, a heap, and a columnstore index. Particionar uma tabela divide a tabela em grupos menores de linhas de acordo com um intervalo de valores de coluna.Partitioning a table divides the table into smaller groups of rows according to a range of column values. Geralmente, ele é usado para gerenciar os dados.It is often used for managing the data. Por exemplo, você pode criar uma partição para cada ano de dados e usar a alternância de partição para arquivar dados em um armazenamento mais barato.For example, you could create a partition for each year of data, and then use partition switching to archive data to less expensive storage. A alternância de partição funciona em índices columnstore e facilita a movimentação de uma partição de dados para outro local.Partition switching works on columnstore indexes and makes it easy to move a partition of data to another location.

Os rowgroups sempre são definidos dentro de uma partição de tabela.Rowgroups are always defined within a table partition. Quando um índice columnstore é particionado, cada partição tem seus próprios rowgroups compactados e rowgroups delta.When a columnstore index is partitioned, each partition has its own compressed rowgroups and delta rowgroups.

Cada partição pode ter vários rowgroups deltaEach partition can have multiple delta rowgroups

Cada partição pode ter mais de um rowgroup delta.Each partition can have more than one delta rowgroups. Quando o índice columnstore precisar adicionar dados a um rowgroup delta e o rowgroup delta estiver bloqueado, o índice columnstore tentará obter um bloqueio em um rowgroup delta diferente.When the columnstore index needs to add data to a delta rowgroup and the delta rowgroup is locked, the columnstore index will try to obtain a lock on a different delta rowgroup. Se não houver nenhum rowgroup delta disponível, o índice columnstore criará um novo rowgroup delta.If there are no delta rowgroups available, the columnstore index will create a new delta rowgroup. Por exemplo, uma tabela com 10 partições poderia facilmente ter 20 ou mais rowgroups delta.For example, a table with 10 partitions could easily have 20 or more delta rowgroups.

Você pode combinar índices columnstore e rowstore na mesma tabelaYou can combine columnstore and rowstore indexes on the same table

Um índice não clusterizado contém uma cópia de parte ou de todas as linhas e colunas na tabela subjacente.A nonclustered index contains a copy of part or all of the rows and columns in the underlying table. O índice é definido como uma ou mais colunas da tabela e tem uma condição opcional que filtra as linhas.The index is defined as one or more columns of the table, and has an optional condition that filters the rows.

A partir do SQL Server 2016 (13.x)SQL Server 2016 (13.x), é possível criar um índice columnstore não clusterizado atualizável em uma tabela rowstore.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create an updatable nonclustered columnstore index on a rowstore table. O índice columnstore armazena uma cópia dos dados, portanto, você precisa de armazenamento extra.The columnstore index stores a copy of the data so you do need extra storage. No entanto, os dados no índice columnstore serão compactados em um tamanho menor do que a tabela rowstore precisa.However, the data in the columnstore index will compress to a smaller size than the rowstore table requires. Com isso, você pode executar análises no índice columnstore e transações no índice rowstore ao mesmo tempo.By doing this, you can run analytics on the columnstore index and transactions on the rowstore index at the same time. O repositório de colunas é atualizado quando dados são alterados na tabela rowstore, assim, ambos os índices trabalham com os mesmos dados.The column store is updated when data changes in the rowstore table, so both indexes are working against the same data.

A partir do SQL Server 2016 (13.x)SQL Server 2016 (13.x), é possível ter um ou mais índices rowstore não clusterizados em um índice columnstore.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can have one or more nonclustered rowstore indexes on a columnstore index. Fazendo isso, você pode executar buscas de tabela eficientes no columnstore subjacente.By doing this, you can perform efficient table seeks on the underlying columnstore. Outras opções também são disponibilizadas.Other options become available too. Por exemplo, você pode impor uma restrição de chave primária usando uma restrição UNIQUE na tabela rowstore.For example, you can enforce a primary key constraint by using a UNIQUE constraint on the rowstore table. Como um valor não exclusivo não poderá ser inserido na tabela rowstore, o SQL Server não pode inserir o valor no columnstore.Since an non-unique value will fail to insert into the rowstore table, SQL Server cannot insert the value into the columnstore.

Considerações sobre desempenhoPerformance considerations

  • A definição do índice columnstore não clusterizado oferece suporte ao uso de uma condição filtrada.The nonclustered columnstore index definition supports using a filtered condition. Para minimizar o impacto no desempenho da adição de um índice columnstore em uma tabela OLTP, use uma condição filtrada para criar um índice columnstore não clusterizado apenas nos dados inativos da sua carga de trabalho operacional.To minimize the performance impact of adding a columnstore index on an OLTP table, use a filtered condition to create a nonclustered columnstore index on only the cold data of your operational workload.

  • Uma tabela na memória pode ter um índice columnstore.An in-memory table can have one columnstore index. Você pode criá-lo quando a tabela for criada ou adicioná-lo mais tarde com ALTER TABLE (Transact-SQL).You can create it when the table is created or add it later with ALTER TABLE (Transact-SQL). Antes do SQL Server 2016 (13.x)SQL Server 2016 (13.x), somente uma tabela baseada em disco podia ter um índice columnstore.Before SQL Server 2016 (13.x)SQL Server 2016 (13.x), only a disk-based table could have a columnstore index.

Para obter mais informações, consulte Índices columnstore – desempenho de consultas.For more information, refer to Columnstore indexes - Query performance.

Diretrizes de designDesign Guidance

  • Uma tabela rowstore pode ter um índice columnstore não clusterizado atualizável.A rowstore table can have one updateable nonclustered columnstore index. Antes do SQL Server 2014 (12.x)SQL Server 2014 (12.x), o índice columnstore não clusterizado era somente leitura.Before SQL Server 2014 (12.x)SQL Server 2014 (12.x), the nonclustered columnstore index was read-only.

Para obter mais informações, consulte Índices columnstore – diretrizes de design.For more information, refer to Columnstore indexes - Design Guidance.

Diretrizes de design de índice de hashHash Index Design Guidelines

Todas as tabelas com otimização de memória devem ter, pelo menos, um índice, porque são os índices que conectam as linhas.All memory-optimized tables must have at least one index, because it is the indexes that connect the rows together. Em uma tabela com otimização de memória, cada índice também tem otimização de memória.On a memory-optimized table, every index is also memory-optimized. Índices de hash são um dos tipos de índice possíveis em uma tabela com otimização de memória.Hash indexes are one of the possible index types in a memory-optimized table. Para obter mais informações, consulte Índices para tabelas com otimização de memória.For more information, see Indexes for Memory-Optimized Tables.

Aplica-se a: do SQL Server 2014 (12.x)SQL Server 2014 (12.x) ao SQL Server 2017SQL Server 2017.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017.

Arquitetura de índice de hashHash Index Architecture

Um índice de hash consiste em uma matriz de ponteiros e cada elemento da matriz é chamado um bucket de hash.A hash index consists of an array of pointers, and each element of the array is called a hash bucket.

  • Cada bucket tem 8 bytes, que são usados para armazenar o endereço de memória de uma lista de links de entradas de chave.Each bucket is 8 bytes, which are used to store the memory address of a link list of key entries.
  • Cada entrada é um valor de uma chave de índice, mais o endereço de sua linha correspondente na tabela com otimização de memória subjacente.Each entry is a value for an index key, plus the address of its corresponding row in the underlying memory-optimized table.
  • Cada um dos pontos de entrada para a próxima entrada em uma lista de links de entradas, todos encadeados no bucket atual.Each entry points to the next entry in a link list of entries, all chained to the current bucket.

O número de buckets deve ser especificado no momento da definição do índice:The number of buckets must be specified at index definition time:

  • Quanto menor a proporção de buckets para linhas ou valores distintos, maior será a lista média de links de bucket.The lower the ratio of buckets to table rows or to distinct values, the longer the average bucket link list will be.
  • Listas de links curtas executam com mais rapidez do que listas de links longas.Short link lists perform faster than long link lists.
  • O número máximo de buckets em índices de hash é de 1.073.741.824.The maximum number of buckets in hash indexes is 1,073,741,824.

Dica

Para determinar a BUCKET_COUNT certa para seus dados, consulte Configurando a contagem de buckets do índice de hash.To determine the right BUCKET_COUNT for your data, see Configuring the hash index bucket count.

A função de hash é aplicada às colunas de chave do índice e o resultado da função determina em qual bucket a chave se enquadra.The hash function is applied to the index key columns and the result of the function determines what bucket that key falls into. Cada bucket tem um ponteiro para linhas cujos valores de chave de hash são mapeados para o bucket.Each bucket has a pointer to rows whose hashed key values are mapped to that bucket.

A função de hash usada para índices de hash tem as seguintes características:The hashing function used for hash indexes has the following characteristics:

  • O SQL ServerSQL Server tem uma função de hash que é usada para todos os índices de hash.SQL ServerSQL Server has one hash function that is used for all hash indexes.
  • A função de hash é determinística.The hash function is deterministic. O mesmo valor de chave de entrada sempre é mapeado para o mesmo bucket no índice de hash.The same input key value is always mapped to the same bucket in the hash index.
  • Várias chaves de índice podem ser mapeadas para o mesmo bucket de hash.Multiple index keys may be mapped to the same hash bucket.
  • A função de hash é equilibrada, o que significa que a distribuição de valores de chave do índice em buckets de hash geralmente segue uma distribuição de Poisson ou curva de sino, não uma distribuição linear simples.The hash function is balanced, meaning that the distribution of index key values over hash buckets typically follows a Poisson or bell curve distribution, not a flat linear distribution.
  • A distribuição de Poisson não é uma distribuição uniforme.Poisson distribution is not an even distribution. Os valores de chave de índice não são distribuídos uniformemente nos buckets de hash.Index key values are not evenly distributed in the hash buckets.
  • Se duas chaves de índice forem mapeadas para o mesmo bucket de hash, haverá uma colisão de hash.If two index keys are mapped to the same hash bucket, there is a hash collision. Um número grande de colisões de hash pode afetar o desempenho das operações de leitura.A large number of hash collisions can have a performance impact on read operations. Uma meta realista é que 30% dos buckets contenham dois valores de chave diferentes.A realistic goal is for 30% of the buckets contain two different key values.

A interação entre o índice de hash e os buckets é resumida na imagem a seguir.The interplay of the hash index and the buckets is summarized in the following image.

hekaton_tables_23dhekaton_tables_23d

Configurando o número de buckets do índice de hashConfiguring the hash index bucket count

O número de buckets do índice de hash é especificado no momento da criação do índice e pode ser alterado com a sintaxe ALTER TABLE...ALTER INDEX REBUILD.The hash index bucket count is specified at index create time, and can be changed using the ALTER TABLE...ALTER INDEX REBUILD syntax.

Na maioria dos casos, o ideal é que a contagem de buckets deve estar entre 1 e 2 vezes o número de valores distintos na chave de índice.In most cases the bucket count would ideally be between 1 and 2 times the number of distinct values in the index key.
Talvez nem sempre seja possível prever quantos valores determinada chave de índice tem ou terá.You may not always be able to predict how many values a particular index key may have, or will have. Em geral, o desempenho ainda será bom se o valor de BUCKET_COUNT estiver dentro de 10 vezes o número real de valores de chave, e valores superestimados são geralmente melhores que os subestimados.Performance is usually still good if the BUCKET_COUNT value is within 10 times of the actual number of key values, and overestimating is generally better than underestimating.

Um número muito pequeno de buckets tem as seguintes desvantagens:Too few buckets has the following drawbacks:

  • Mais colisões de hash de valores de chave distintos.More hash collisions of distinct key values.
  • Cada valor distinto é forçado a compartilhar o mesmo bucket com um valor distinto diferente.Each distinct value is forced to share the same bucket with a different distinct value.
  • O comprimento médio de cadeia por bucket aumenta.The average chain length per bucket grows.
  • Quanto maior é a cadeia de bucket, mais lentas são as pesquisas de igualdade no índice.The longer the bucket chain, the slower the speed of equality lookups in the index.

Um número muito grande de buckets tem as seguintes desvantagens:Too many buckets has the following drawbacks:

  • Um número de buckets muito alto pode resultar em mais buckets vazios.Too high a bucket count might result in more empty buckets.
  • Buckets vazios afetam o desempenho de verificações de índice completas.Empty buckets impact the performance of full index scans. Se elas forem executadas regularmente, considere escolher uma contagem de buckets próximo ao número de valores de chave de índice distintos.If those are performed regularly, consider picking a bucket count close to the number of distinct index key values.
  • Buckets vazios usam a memória, embora cada bucket use apenas 8 bytes.Empty buckets use memory, though each bucket uses only 8 bytes.

Observação

A adição de mais buckets não contribui para reduzir o encadeamento de entradas que compartilham um valor duplicado.Adding more buckets does nothing to reduce the chaining together of entries that share a duplicate value. A taxa de duplicação de valor é usada para decidir se um hash é o tipo de índice apropriado, não para calcular o número de buckets.The rate of value duplication is used to decide whether a hash is the appropriate index type, not to calculate the bucket count.

Considerações sobre desempenhoPerformance considerations

O desempenho de um índice de hash é:The performance of a hash index is:

  • Excelente quando o predicado na cláusula WHERE especifica um valor exato para cada coluna na chave de índice de hash.Excellent when the predicate in the WHERE clause specifies an exact value for each column in the hash index key. Um índice de hash reverterá para um exame de acordo com um predicado de desigualdade.A hash index will revert to a scan given an inequality predicate.
  • Fraco quando o predicado na cláusula WHERE procura um intervalo de valores na chave de índice.Poor when the predicate in the WHERE clause looks for a range of values in the index key.
  • Fraco quando o predicado na cláusula WHERE estipula um valor específico para a primeira coluna de uma chave de índice de hash de duas colunas, mas não especifica um valor para outras colunas da chave.Poor when the predicate in the WHERE clause stipulates one specific value for the first column of a two column hash index key, but does not specify a value for other columns of the key.

Dica

O predicado deve incluir todas as colunas na chave de índice de hash.The predicate must include all columns in the hash index key. O índice de hash requer uma chave (para submeter a hash) para realizar a busca no índice.The hash index requires a key (to hash) to seek into the index. Se uma chave de índice consistir em duas colunas e a cláusula WHERE fornecer apenas a primeira coluna, SQL ServerSQL Server não terá uma chave completa para o hash.If an index key consists of two columns and the WHERE clause only provides the first column, SQL ServerSQL Server does not have a complete key to hash. Isso resultará em um plano de consulta de exame de índice.This will result in an index scan query plan.

Se um índice de hash for usado e o número de chaves de índice exclusivo for 100 vezes (ou mais) a contagem de linhas, considere a possibilidade de aumentar o número de buckets para evitar cadeias de linhas grandes ou use um índice não clusterizado.If a hash index is used and the number of unique index keys is 100 times (or more) than the row count, consider either increasing to a larger bucket count to avoid large row chains, or use a nonclustered index instead.

Considerações sobre declaraçãoDeclaration considerations

Um índice de hash apenas pode existir em uma tabela com otimização de memória.A hash index can exist only on a memory-optimized table. Ele não pode existir em uma tabela baseada em disco.It cannot exist on a disk-based table.

Um índice de hash pode ser declarado como:A hash index can be declared as:

  • UNIQUE ou pode usar Não Exclusivo como padrão.UNIQUE, or can default to Non-Unique.
  • NONCLUSTERED, que é o padrão.NONCLUSTERED, which is the default.

Este é um exemplo da sintaxe para criar um índice de hash fora da instrução CREATE TABLE:The following is an example of the syntax to create a hash index, outside of the CREATE TABLE statement:

ALTER TABLE MyTable_memop  
ADD INDEX ix_hash_Column2 UNIQUE  
HASH (Column2) WITH (BUCKET_COUNT = 64);

Versões de linha e coleta de lixoRow versions and garbage collection

Em uma tabela com otimização de memória, quando uma linha é afetada por um UPDATE, a tabela cria uma versão atualizada da linha.In a memory-optimized table, when a row is affected by an UPDATE, the table creates an updated version of the row. Durante a transação de atualização, é possível que outras sessões consigam ler a versão mais antiga da linha, evitando a lentidão de desempenho associada a um bloqueio de linha.During the update transaction, other sessions might be able to read the older version of the row and thereby avoid the performance slowdown associated with a row lock.

O índice de hash também pode ter versões diferentes de suas entradas para acomodar a atualização.The hash index might also have different versions of its entries to accommodate the update.

Posteriormente, quando as versões mais antigas não forem mais necessárias, um thread de GC (coleta de lixo) percorrerá os buckets e suas listas de links para limpar as entradas antigas.Later when the older versions are no longer needed, a garbage collection (GC) thread traverses the buckets and their link lists to clean away old entries. A execução do thread da GC é melhor se os tamanhos de cadeia de lista de link são curtos.The GC thread performs better if the link list chain lengths are short. Para obter mais informações, consulte Coleta de lixo de OLTP in-memory.For more information, refer to In-Memory OLTP Garbage Collection.

Diretrizes de design de índice não clusterizado com otimização de memóriaMemory-Optimized Nonclustered Index Design Guidelines

Índices não clusterizados são um dos tipos de índice possíveis em uma tabela com otimização de memória.Nonclustered indexes are one of the possible index types in a memory-optimized table. Para obter mais informações, consulte Índices para tabelas com otimização de memória.For more information, see Indexes for Memory-Optimized Tables.

Aplica-se a: do SQL Server 2014 (12.x)SQL Server 2014 (12.x) ao SQL Server 2017SQL Server 2017.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017.

Arquitetura de índice não clusterizado na memóriaIn-memory Nonclustered Index Architecture

Índices não clusterizados na memória são implementados com uma estrutura de dados chamada Árvore Bw, originalmente concebida e descrita pela Microsoft Research em 2011.In-memory nonclustered indexes are implemented using a data structure called a Bw-Tree, originally envisioned and described by Microsoft Research in 2011. Uma Árvore Bw é uma variação livre de bloqueios e travas de uma Árvore B.A Bw-Tree is a lock and latch-free variation of a B-Tree. Veja mais detalhes em A Árvore BW: uma árvore B para novas plataformas de hardware.For more details please see The Bw-Tree: A B-tree for New Hardware Platforms.

Em um nível muito alto, a Árvore BW pode ser interpretada como um mapa de páginas organizadas por ID de página (PidMap), um recurso para alocar e reutilizar IDs de página (PidAlloc) e um conjunto de páginas vinculadas no mapa de páginas umas às outras.At a very high level the Bw-Tree can be understood as a map of pages organized by page ID (PidMap), a facility to allocate and reuse page IDs (PidAlloc) and a set of pages linked in the page map and to each other. Esses três subcomponentes de alto nível formam a estrutura básica interna de uma Árvore Bw.These three high level sub-components make up the basic internal structure of a Bw-Tree.

A estrutura é semelhante a uma Árvore B normal, pois cada página tem um conjunto de valores de chave ordenados e existem níveis no índice que apontam para um nível inferior e os níveis folha apontam para uma linha de dados.The structure is similar to a normal B-Tree in the sense that each page has a set of key values that are ordered and there are levels in the index each pointing to a lower level and the leaf levels point to a data row. Entretanto, há várias diferenças.However there are several differences.

Assim como os índices de hash, várias linhas de dados podem ser vinculadas (versões).Just like hash indexes, multiple data rows can be linked together (versions). Os ponteiros de página entre os níveis são IDs de página lógicas, que são deslocamentos em uma tabela de Mapeamento de Página, que, por sua vez, tem o endereço físico de cada página.The page pointers between the levels are logical page IDs, which are offsets into a page mapping table, that in turn has the physical address for each page.

Não existem atualizações in-loco de páginas de índice.There are no in-place updates of index pages. Novas páginas delta são introduzidas para essa finalidade.New delta pages are introduced for this purpose.

  • Nenhum bloqueio ou trava é necessário para atualizações de página.No latching or locking is required for page updates.
  • Páginas de índice não são de tamanho fixo.Index pages are not a fixed size.

O valor de chave em cada página de nível não folha mostrada é o valor mais alto que o filho para o qual ele aponta contém e cada linha também contém essa ID de página lógica.The key value in each non-leaf level page depicted is the highest value that the child that it points to contains and each row also contains that page logical page ID. Nas páginas no nível de folha, juntamente com o valor de chave, ele contém o endereço físico da linha de dados.On the leaf-level pages, along with the key value, it contains the physical address of the data row.

Pesquisas de ponto são semelhantes às Árvores B, exceto pelo fato de as páginas serem vinculadas em uma única direção e o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine seguir ponteiros de página direita, em que cada página não folha tem o valor mais alto de seu filho, em vez do valor mais baixo, como em uma Árvore B.Point lookups are similar to B-Trees except that because pages are linked in only one direction, the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine follows right page pointers, where each non-leaf pages has the highest value of its child, rather than lowest value as in a B-Tree.

Se uma página no nível de Folha precisar ser alterada, o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine não modificará a página propriamente dita.If a Leaf-level page has to change, the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine does not modify the page itself. Em vez disso, o Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine criará um registro delta que descreve a alteração e o acrescentará à página anterior.Rather, the Mecanismo de Banco de Dados do SQL ServerSQL Server Database Engine creates a delta record that describes the change, and appends it to the previous page. Em seguida, ele também atualizará o endereço da tabela de mapa da página anterior para o endereço do registro delta, que agora se torna o endereço físico dessa página.Then it also updates the page map table address for that previous page, to the address of the delta record which now becomes the physical address for this page.

Há três operações diferentes que podem ser necessárias para gerenciar a estrutura de uma Árvore Bw: consolidação, divisão e mesclagem.There are three different operations that can be required for managing the structure of a Bw-Tree: consolidation, split and merge.

Consolidação de deltaDelta Consolidation

Uma cadeia grande de registros delta pode acabar prejudicando o desempenho de pesquisas, pois isso pode significar que estamos percorrendo cadeias longas durante a pesquisa por meio de um índice.A long chain of delta records can eventually degrade search performance as it could mean we are traversing long chains when searching through an index. Se um novo registro delta for adicionado a uma cadeia que já tem 16 elementos, as alterações nos registros delta serão consolidadas na página de índice referenciada e, em seguida, a página será recriada, incluindo as alterações indicadas pelo novo registro delta que dispararam a consolidação.If a new delta record is added to a chain that already has 16 elements, the changes in the delta records will be consolidated into the referenced index page, and the page will then be rebuilt, including the changes indicated by the new delta record that triggered the consolidation. A página recém-recriada terá a mesma ID de página, mas um novo endereço de memória.The newly rebuilt page will have the same page ID but a new memory address.

hekaton_tables_23ehekaton_tables_23e

Dividir páginaSplit page

Uma página de índice na Árvore Bw aumenta conforme a necessidade, começando com o armazenamento de uma única linha até um máximo de 8 KB.An index page in Bw-Tree grows on as-needed basis starting from storing a single row to storing a maximum of 8 KB. Depois que a página de índice aumentar para 8 KB, uma nova inserção de uma única linha fará com que a página de índice seja dividida.Once the index page grows to 8 KB, a new insert of a single row will cause the index page to split. Para uma página interna, isso significa que quando não há mais nenhum espaço para adição de outro ponteiro e valor de chave, e para uma página de folha, isso significa que a linha será muito grande para se ajustar à página depois que todos os registros delta forem incorporados.For an internal page, this means when there is no more room to add another key value and pointer, and for a leaf page, it means that the row would be too big to fit on the page once all the delta records are incorporated. As informações de estatísticas no cabeçalho de uma página de folha controlam a quantidade de espaço que será necessário para consolidar os registros delta e essas informações são ajustadas a cada adição de um novo registro delta.The statistics information in the page header for a leaf page keeps track of how much space would be required to consolidate the delta records, and that information is adjusted as each new delta record is added.

Uma operação de Divisão é feita em duas etapas atômicas.A Split operation is done in two atomic steps. Na imagem abaixo, suponha que uma página de Folha force uma divisão porque uma chave com o valor 5 está sendo inserida e existe uma página não folha apontando para o final da página atual no nível de folha (valor de chave 4).In the picture below, assume a Leaf-page forces a split because a key with value 5 is being inserted, and a non-leaf page exists pointing to the end of the current Leaf-level page (key value 4).

hekaton_tables_23fhekaton_tables_23f

Etapa 1: alocar duas novas páginas P1 e P2 e dividir as linhas da página P1 antiga nessas novas páginas, incluindo a linha recém-inserida.Step 1: Allocate two new pages P1 and P2, and split the rows from old P1 page onto these new pages, including the newly inserted row. Um novo slot na Tabela de Mapeamento de Página é usado para armazenar o endereço físico da página P2.A new slot in Page Mapping Table is used to store the physical address of page P2. Essas páginas, P1 e P2, ainda não são acessíveis a todas as operações simultâneas.These pages, P1 and P2 are not accessible to any concurrent operations yet. Além disso, o ponteiro lógico de P1 para P2 é definido.In addition, the logical pointer from P1 to P2 is set. Em seguida, em uma única etapa atômica, atualize a Tabela de Mapeamento de Página para alterar o ponteiro do P1 antigo para o novo P1.Then, in one atomic step update the Page Mapping Table to change the pointer from old P1 to new P1.

Etapa 2: a página não folha aponta para P1, mas não há nenhum ponteiro direto de uma página não folha para P2.Step 2: The non-leaf page points to P1 but there is no direct pointer from a non-leaf page to P2. P2 só é acessível por meio de P1.P2 is only reachable via P1. Para criar um ponteiro de uma página não folha para P2, aloque uma nova página não folha (página de índice interna), copie todas as linhas da página não folha antiga e adicione uma nova linha para que ela aponte para P2.To create a pointer from a non-leaf page to P2, allocate a new non-leaf page (internal index page), copy all the rows from old non-leaf page, and add a new row to point to P2. Depois que isso for feito, em uma única etapa atômica, atualize a Tabela de Mapeamento de Página para alterar o ponteiro da página não folha antiga para a nova página não folha.Once this is done, in one atomic step, update the Page Mapping Table to change the pointer from old non-leaf page to new non-leaf page.

Mesclar páginaMerge page

Quando uma operação DELETE resultar em uma página com menos de 10% do tamanho máximo da página (atualmente, 8 KB) ou com uma única linha nela, essa página será mesclada com uma página contígua.When a DELETE operation results in a page having less than 10% of the maximum page size (currently 8 KB), or with a single row on it, that page will be merged with a contiguous page.

Quando uma linha é excluída de uma página, um registro delta para a exclusão é adicionado.When a row is deleted from a page, a delta record for the delete is added. Além disso, é feita uma verificação para determinar se a página de índice (página não folha) se qualifica para a Mesclagem.Additionally, a check is made to determine if the index page (non-leaf page) qualifies for Merge. Essa verificação verifica se o espaço restante após a exclusão da linha será menor que 10% do tamanho máximo da página.This check verifies if the remaining space after deleting the row will be less than 10% of maximum page size. Se ela se qualificar, a Mesclagem será executada em três etapas atômicas.If it does qualify, the Merge is performed in three atomic steps.

Na imagem abaixo, suponha que uma operação DELETE excluirá o valor de chave 10.In the picture below, assume a DELETE operation will delete the key value 10.

hekaton_tables_23ghekaton_tables_23g

Etapa 1: uma página delta que representa o valor da chave 10 (triângulo azul) é criada e seu ponteiro na página não folha Pp1 é definido como a nova página delta.Step 1: A delta page representing key value 10 (blue triangle) is created and its pointer in the non-leaf page Pp1 is set to the new delta page. Além disso, uma página especial delta de mesclagem (triângulo verde) é criada e é vinculada para apontar para a página delta.Additionally a special merge-delta page (green triangle) is created, and it is linked to point to the delta page. Neste estágio, nenhuma das páginas (página delta e página delta de mesclagem) é visível para transação simultânea.At this stage, both pages (delta page and merge-delta page) are not visible to any concurrent transaction. Em uma única etapa atômica, o ponteiro para a página nível Folha P1 na Tabela de Mapeamento de Página é atualizado para apontar para a página delta de mesclagem.In one atomic step, the pointer to the Leaf-level page P1 in the Page Mapping Table is updated to point to the merge-delta page. Após essa etapa, a entrada do valor de chave 10 em Pp1 agora apontará para a página delta de mesclagem.After this step, the entry for key value 10 in Pp1 now points to the merge-delta page.

Etapa 2: a linha que representa o valor da chave 7 na página não folha Pp1 precisa ser removida e a entrada do valor da chave 10 atualizada para apontar para P1.Step 2: The row representing key value 7 in the non-leaf page Pp1 needs to be removed, and the entry for key value 10 updated to point to P1. Para fazer isso, uma nova página não folha Pp2 é alocada e todas as linhas de Pp1 são copiadas, exceto a linha que representa o valor de chave 7; em seguida, a linha do valor de chave 10 é atualizada para apontar para a página P1.To do this, a new non-leaf page Pp2 is allocated and all the rows from Pp1 are copied except for the row representing key value 7; then the row for key value 10 is updated to point to page P1. Depois que isso é feito, em uma única etapa atômica, a entrada da Tabela de Mapeamento de Página que aponta para Pp1 é atualizada para apontar para Pp2.Once this is done, in one atomic step, the Page Mapping Table entry pointing to Pp1 is updated to point to Pp2. Pp1 não é mais acessível.Pp1 is no longer reachable.

Etapa 3: as páginas de nível folha P2 e P1 são mescladas e as páginas delta, removidas.Step 3: The Leaf-level pages P2 and P1 are merged and the delta pages removed. Para fazer isso, uma nova página P3 é alocada e as linhas de P2 e P1 são mescladas e as alterações da página delta são incluídas no novo P3.To do this, a new page P3 is allocated and the rows from P2 and P1 are merged, and the delta page changes are included in the new P3. Em seguida, em uma única etapa atômica, a entrada da Tabela de Mapeamento de Página que aponta para a página P1 é atualizada para apontar para a página P3.Then, in one atomic step, the Page Mapping Table entry pointing to page P1 is updated to point to page P3.

Considerações sobre desempenhoPerformance considerations

O desempenho de um índice não clusterizado é melhor do que o de índices de hash não clusterizados ao consultar uma tabela com otimização de memória com predicados de desigualdade.The performance of a nonclustered index is better than nonclustered hash indexes when querying a memory-optimized table with inequality predicates.

Observação

Uma coluna em uma tabela com otimização de memória pode fazer parte de um índice de hash e de um índice não clusterizado.A column in a memory-optimized table can be part of both a hash index and a nonclustered index.

Dica

Quando uma coluna em colunas de chave de índice não clusterizado tem muitos valores duplicados, o desempenho pode ser prejudicado para atualizações, inserções e exclusões.When a column in a nonclustered index key columns have many duplicate values, performance can degrade for updates, inserts, and deletes. Uma maneira de melhorar o desempenho nessa situação é adicionar outra coluna ao índice não clusterizado.One way to improve performance in this situation is to add another column to the nonclustered index.

Leitura adicionalAdditional Reading

CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
CREATE XML INDEX (Transact-SQL)CREATE XML INDEX (Transact-SQL)
CREATE SPATIAL INDEX (Transact-SQL) CREATE SPATIAL INDEX (Transact-SQL)
Reorganizar e recompilar índices Reorganize and Rebuild Indexes
Melhorando o desempenho com exibições indexadas do SQL Server 2008Improving Performance with SQL Server 2008 Indexed Views
Tabelas e índices particionadosPartitioned Tables and Indexes
Criar uma chave primária Create a Primary Key
Índices para tabelas com otimização de memóriaIndexes for Memory-Optimized Tables
Visão geral de Índices columnstoreColumnstore Indexes overview
Solução de problemas de índices de hash para tabelas com otimização de memória Troubleshooting Hash Indexes for Memory-Optimized Tables
Exibições de gerenciamento dinâmico de tabela com otimização de memória (Transact-SQL) Memory-Optimized Table Dynamic Management Views (Transact-SQL)
Exibições e funções de gerenciamento dinâmico relacionadas ao índice (Transact-SQL) Index Related Dynamic Management Views and Functions (Transact-SQL)
Índices em colunas computadas Indexes on Computed Columns
Índices e ALTER TABLE Indexes and ALTER TABLE
Desfragmentação de índice adaptávelAdaptive Index Defrag