Guía de diseño y de arquitectura de índices de SQL ServerSQL Server Index Architecture and Design Guide

SE APLICA A: síSQL Server síAzure SQL Database síAzure SQL Data Warehouse síAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Los índices mal diseñados y la falta de índices constituyen las principales fuentes de atascos en aplicaciones de base de datos.Poorly designed indexes and a lack of indexes are primary sources of database application bottlenecks. El diseño eficaz de los índices tiene gran importancia para conseguir un buen rendimiento de una base de datos y una aplicación.Designing efficient indexes is paramount to achieving good database and application performance. Esta guía de diseño de índices de SQL ServerSQL Server contiene información sobre la arquitectura de índices y prácticas recomendadas que le ayudarán a diseñar índices eficaces que resuelvan las necesidades de la aplicación.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.

En esta guía se da por supuesto que el lector tiene información general sobre los tipos de índice disponibles en SQL ServerSQL Server.This guide assumes the reader has a general understanding of the index types available in SQL ServerSQL Server. Para obtener una descripción general de los tipos de índice, vea Tipos de índice.For a general description of index types, see Index Types.

En esta guía se tratan los siguientes tipos de índices:This guide covers the following types of indexes:

  • ClústerClustered
  • No agrupadoNonclustered
  • ÚnicoUnique
  • FiltradoFiltered
  • columnstoreColumnstore
  • HashHash
  • Índice no agrupado optimizado para memoriaMemory-Optimized Nonclustered

Para obtener información sobre los índices XML, vea Índices XML en la sección Información general.For information about XML indexes, see XML Indexes Overview.

Para obtener información sobre los índices espaciales, vea Información general sobre los índices espaciales.For information about Spatial indexes, see Spatial Indexes Overview.

Para obtener más información sobre los índices de texto completo, vea Rellenar índices de texto completo.For information about Full-text indexes, see Populate Full-Text Indexes.

Conceptos básicos del diseño de índicesIndex Design Basics

Un índice es una estructura en disco o en memoria asociada con una tabla o vista que acelera la recuperación de filas de la tabla o vista.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. Un índice contiene claves generadas a partir de una o varias columnas de la tabla o la vista.An index contains keys built from one or more columns in the table or view. En el caso de los índices en disco, dichas claves están almacenadas en una estructura (árbol B) que permite que SQL Server busque de forma rápida y eficiente la fila o las filas asociadas a los valores de cada clave.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.

Los índices almacenan los datos organizados de forma lógica como una tabla con filas y columnas, y físicamente almacenados en un formato de datos por fila llamado almacén de filas 1, o bien en un formato de datos por columna llamado almacén de columnas .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.

La selección de los índices apropiados para una base de datos y su carga de trabajo es una compleja operación que busca el equilibrio entre la velocidad de la consulta y el costo de actualización.The selection of the right indexes for a database and its workload is a complex balancing act between query speed and update cost. Los índices estrechos, o con pocas columnas en la clave de índice, necesitan menos espacio en el disco y son menos susceptibles de provocar sobrecargas debido a su mantenimiento.Narrow indexes, or indexes with few columns in the index key, require less disk space and maintenance overhead. Por otra parte, la ventaja de los índices anchos es que cubren más consultas.Wide indexes, on the other hand, cover more queries. Puede que tenga que experimentar con distintos diseños antes de encontrar el índice más eficaz.You may have to experiment with several different designs before finding the most efficient index. Es posible agregar, modificar y quitar índices sin que esto afecte al esquema de la base de datos o al diseño de la aplicación.Indexes can be added, modified, and dropped without affecting the database schema or application design. Por lo tanto, no debe dudar en experimentar con índices diferentes.Therefore, you should not hesitate to experiment with different indexes.

El optimizador de consultas de SQL ServerSQL Server elige de forma confiable el índice más eficaz en la mayoría de los casos.The query optimizer in SQL ServerSQL Server reliably chooses the most effective index in the vast majority of cases. La estrategia general de diseño de índices debe proporcionar una buena selección de índices al optimizador de consultas y confiar en que tomará la decisión correcta.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. Así se reduce el tiempo de análisis y se obtiene un buen rendimiento en diversas situaciones.This reduces analysis time and produces good performance over a variety of situations. Para saber qué índices utiliza el optimizador de consultas para determinada consulta, en SQL Server Management StudioSQL Server Management Studio, en el menú Consulta , seleccione Incluir plan de ejecución 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.

No equipare siempre la utilización de índices con un buen rendimiento ni el buen rendimiento al uso eficaz del índice.Do not always equate index usage with good performance, and good performance with efficient index use. Si la utilización de un índice contribuyera siempre a producir el mejor rendimiento, el trabajo del optimizador de consultas sería muy sencillo.If using an index always helped produce the best performance, the job of the query optimizer would be simple. En realidad, una elección incorrecta de índice puede provocar un rendimiento bajo.In reality, an incorrect index choice can cause less than optimal performance. Por tanto, la tarea del optimizador de consultas consiste en seleccionar un índice o una combinación de índices solo si mejora el rendimiento, y evitar la recuperación indizada cuando afecte al mismo.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 Los almacenes de filas han sido la forma tradicional de almacenar los datos de una tabla relacional.1 Rowstore has been the traditional way to store relational table data. En SQL ServerSQL Server, "almacén de filas" hace referencia a la tabla en la que el formato de almacenamiento de datos subyacente es un montón, un árbol B (índice agrupado) o una tabla optimizada para memoria.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.

Tareas del diseño de índicesIndex Design Tasks

Las siguientes tareas componen la estrategia recomendada para el diseño de índices:The follow tasks make up our recommended strategy for designing indexes:

  1. Comprender las características de la propia base de datos.Understand the characteristics of the database itself.

    • Por ejemplo, si es una base de datos de procesamiento de transacciones en línea (OLTP) con modificaciones de datos frecuentes que deben tener un alto rendimiento.For example, is it an online transaction processing (OLTP) database with frequent data modifications that must sustain a high throughput. A partir de SQL Server 2014 (12.x)SQL Server 2014 (12.x), las tablas y los índices optimizados para memoria son especialmente adecuados en este escenario, ya que proporcionan un diseño sin bloqueos temporales.Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), memory-optimized tables and indexes are especially appropriate for this scenario, by providing a latch-free design. Para obtener más información, en esta guía podrá ver Índices de tablas optimizadas para memoria, Nonclustered Index for Memory-Optimized Tables Design Guidelines (Guía de diseño de índices no agrupados para tablas optimizadas para memoria) y Hash Index for Memory-Optimized Tables Design Guidelines (Guía de diseño de índices de hash para tablas optimizadas para memoria).For more information, see Indexes for Memory-Optimized Tables, or Nonclustered Index for Memory-Optimized Tables Design Guidelines and Hash Index for Memory-Optimized Tables Design Guidelines in this guide.
    • O bien, el ejemplo de una base de datos de sistema de ayuda a la toma de decisiones (DSS) o almacenamiento de datos (OLAP) que debe procesar con rapidez conjuntos de datos muy grandes.Or an example of a Decision Support System (DSS) or data warehousing (OLAP) database that must process very large data sets quickly. A partir de SQL Server 2012 (11.x)SQL Server 2012 (11.x), los índices de almacén de columnas son especialmente adecuados para los conjuntos de datos de almacenamiento de datos comunes.Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), columnstore indexes are especially appropriate for typical data warehousing data sets. Los índices de almacén de columnas pueden transformar la experiencia de almacenamiento de datos de los usuarios, ya que permite un rendimiento más rápido en las consultas habituales de almacenamiento de datos, como el filtrado, la agregación, la agrupación y la combinación en estrella de consultas.Columnstore indexes can transform the data warehousing experience for users by enabling faster performance for common data warehousing queries such as filtering, aggregating, grouping, and star-join queries. Para obtener más información, vea Introducción a los índices de almacén de columnas o Directrices para diseñar índices de almacén de columnas en esta guía.For more information, see Columnstore Indexes overview, or Columnstore Index Design Guidelines in this guide.
  2. Comprender las características de las consultas utilizadas con frecuencia.Understand the characteristics of the most frequently used queries. Por ejemplo, saber que una consulta utilizada con frecuencia une dos o más tablas facilitará la determinación del mejor tipo de índices que se puede utilizar.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. Comprender las características de las columnas utilizadas en las consultas.Understand the characteristics of the columns used in the queries. Por ejemplo, un índice es idóneo para columnas que tienen un tipo de datos entero y además son columnas con valores NULL o no NULL.For example, an index is ideal for columns that have an integer data type and are also unique or nonnull columns. En el caso de columnas que tengan subconjuntos de datos bien definidos, puede usar un índice filtrado en SQL Server 2008SQL Server 2008 y en versiones 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 obtener más información, vea Directrices generales para diseñar índices filtrados en esta guía.For more information, see Filtered Index Design Guidelines in this guide.

  4. Determinar qué opciones de índice podrían mejorar el rendimiento al crear o mantener el índice.Determine which index options might enhance performance when the index is created or maintained. Por ejemplo, la creación de un índice agrupado en una tabla grande se beneficiaría de la opción de índice ONLINE.For example, creating a clustered index on an existing large table would benefit from the ONLINE index option. La opción ONLINE permite que la actividad simultánea en los datos subyacentes continúe mientras el índice se crea o regenera.The ONLINE option allows for concurrent activity on the underlying data to continue while the index is being created or rebuilt. Para obtener más información, consulte Establecer opciones de índice.For more information, see Set Index Options.

  5. Determinar la ubicación de almacenamiento óptima para el índice.Determine the optimal storage location for the index. Un índice no clúster se puede almacenar en el mismo grupo de archivos que la tabla subyacente o en un grupo distinto.A nonclustered index can be stored in the same filegroup as the underlying table, or on a different filegroup. La ubicación de almacenamiento de índices puede mejorar el rendimiento de las consultas aumentando el rendimiento de las operaciones de E/S en disco.The storage location of indexes can improve query performance by increasing disk I/O performance. Por ejemplo, el almacenamiento de un índice no clúster en un grupo de archivos que se encuentra en un disco distinto que el del grupo de archivos de la tabla puede mejorar el rendimiento, ya que se pueden leer varios discos al mismo tiempo.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.
    O bien, los índices clúster y no clúster pueden utilizar un esquema de particiones en varios grupos de archivos.Alternatively, clustered and nonclustered indexes can use a partition scheme across multiple filegroups. Las particiones facilitan la administración de índices y tablas grandes al permitir el acceso y la administración de subconjuntos de datos rápidamente y con eficacia, mientras se mantiene la integridad de la colección global.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 obtener más información, consulte Partitioned Tables and Indexes.For more information, see Partitioned Tables and Indexes. Al considerar la posibilidad de utilizar particiones, determine si el índice debe alinearse; es decir, si las particiones se crean esencialmente del mismo modo que la tabla o de forma independiente.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.

Directrices generales para diseñar índicesGeneral Index Design Guidelines

Los administradores de bases de datos más experimentados pueden diseñar un buen conjunto de índices, pero esta tarea es muy compleja, consume mucho tiempo y está sujeta a errores, incluso con cargas de trabajo y bases de datos con un grado de complejidad no excesivo.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. La comprensión de las características de la base de datos, las consultas y las columnas de datos facilita el diseño de los índices.Understanding the characteristics of your database, queries, and data columns can help you design optimal indexes.

Consideraciones acerca de la base de datosDatabase Considerations

Cuando diseñe un índice, tenga en cuenta las siguientes directrices acerca de la base de datos:When you design an index, consider the following database guidelines:

  • Si se usa un gran número de índices en una tabla, el rendimiento de las instrucciones INSERT, UPDATE, DELETE y MERGE se verá afectado, ya que todos los índices deben ajustarse adecuadamente a medida que cambian los datos de la tabla.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 ejemplo, si una columna se usa en varios índices y ejecuta una instrucción UPDATE que modifica datos de esa columna, se deben actualizar todos los índices que contengan esa columna, así como la columna de la tabla base subyacente (índice de montón o agrupado).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 crear demasiados índices en tablas que se actualizan con mucha frecuencia y mantenga los índices estrechos, es decir, defínalos con el menor número de columnas posible.Avoid over-indexing heavily updated tables and keep indexes narrow, that is, with as few columns as possible.

    • Utilice un número mayor de índices para mejorar el rendimiento de consultas en tablas con pocas necesidades de actualización, pero con grandes volúmenes de datos.Use many indexes to improve query performance on tables with low update requirements, but large volumes of data. Un gran número de índices contribuye a mejorar el rendimiento de las consultas que no modifican datos, como las instrucciones SELECT, ya que el optimizador de consultas dispone de más índices entre los que elegir para determinar el método de acceso más 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.

  • La indización de tablas pequeñas puede no ser una solución óptima, porque puede provocar que el optimizador de consultas tarde más tiempo en realizar la búsqueda de los datos a través del índice que en realizar un simple recorrido de la tabla.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. De este modo, es posible que los índices de tablas pequeñas no se utilicen nunca; sin embargo, sigue siendo necesario su mantenimiento a medida que cambian los datos de la tabla.Therefore, indexes on small tables might never be used, but must still be maintained as data in the table changes.

  • Los índices en vistas pueden mejorar de forma significativa el rendimiento si la vista contiene agregaciones, combinaciones de tabla o una mezcla de agregaciones y combinaciones.Indexes on views can provide significant performance gains when the view contains aggregations, table joins, or a combination of aggregations and joins. No es necesario hacer referencia de forma explícita a la vista en la consulta para que el optimizador de consultas la utilice.The view does not have to be explicitly referenced in the query for the query optimizer to use it.

  • Utilice el Asistente para la optimización de motor de base de datos para analizar las bases de datos y crear recomendaciones de índices.Use the Database Engine Tuning Advisor to analyze your database and make index recommendations. Para obtener más información, vea Database Engine Tuning Advisor.For more information, see Database Engine Tuning Advisor.

Consideraciones sobre consultasQuery Considerations

Cuando diseñe un índice, tenga en cuenta las siguientes directrices acerca de las consultas:When you design an index, consider the following query guidelines:

  • Cree índices no clúster en las columnas que se usan con frecuencia en predicados y condiciones de combinación de las consultas.Create nonclustered indexes on the columns that are frequently used in predicates and join conditions in queries. Estas son las columnas SARGable1.These are your SARGable1 columns. Sin embargo, debe evitar agregar columnas innecesarias.However, you should avoid adding unnecessary columns. Si agrega demasiadas columnas de índice, puede reducir el espacio en disco y el rendimiento del mantenimiento del índice.Adding too many index columns can adversely affect disk space and index maintenance performance.

  • La utilización de índices puede mejorar el rendimiento de las consultas, ya que los datos necesarios para satisfacer las necesidades de la consulta existen en el propio índice.Covering indexes can improve query performance because all the data needed to meet the requirements of the query exists within the index itself. Es decir, solo se requieren las páginas de índice, y no las páginas de datos de la tabla o el índice clúster, para recuperar los datos solicitados; por lo tanto, se reduce la E/S de disco global.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 ejemplo, una consulta de las columnas a y b en una tabla que tiene un índice compuesto creado en las columnas a, by c puede recuperar los datos especificados del í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

    Los índices de cobertura son la designación para un índice no agrupado que resuelve uno o varios resultados de consulta similares directamente sin acceso a su tabla base y sin incurrir en búsquedas.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. Esos índices tienen todas las columnas no SARGable necesarias en su nivel hoja.Such indexes have all the necessary non-SARGable columns in its leaf level. Esto significa que el índice cubre todas las columnas que devuelve la cláusula SELECT y todos los argumentos WHERE y JOIN.This means that the columns returned by either the SELECT clause and all the WHERE and JOIN arguments are covered by the index. Posiblemente, hay mucho menos E/S para ejecutar la consulta, si el índice es lo suficientemente restringido en comparación con las filas y las columnas de la tabla misma, lo que significa que es un subconjunto real del total de columnas.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 los índices de cobertura cuando seleccione una pequeña porción de una tabla de gran tamaño y donde esa porción pequeña esté definida mediante un predicado fijo, como columnas dispersas que solo contienen unos pocos valores no NULL, por ejemplo.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.

  • Escriba consultas que inserten o modifiquen tantas filas como sea posible en una sola instrucción, en lugar de utilizar varias consultas para actualizar las mismas 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. Al utilizar solo una instrucción, se puede aprovechar el mantenimiento de índices optimizados.By using only one statement, optimized index maintenance could be exploited.

  • Analice el tipo de la consulta y cómo se utilizan las columnas en ella.Evaluate the query type and how columns are used in the query. Por ejemplo, una columna utilizada en una consulta de coincidencia exacta sería una buena candidata para un índice no clúster o clúster.For example, a column used in an exact-match query type would be a good candidate for a nonclustered or clustered index.

1 El término SARGable en las bases de datos relacionales se refiere a un predicado Search ARGument-able que puede usar un índice para acelerar la ejecución de la 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.

Consideraciones sobre columnasColumn Considerations

Cuando diseñe un índice, tenga en cuenta las siguientes directrices acerca de las columnas:When you design an index consider the following column guidelines:

  • Utilice una longitud corta en la clave de los índices clúster.Keep the length of the index key short for clustered indexes. Los índices clúster también mejoran si se crean en columnas únicas o que no admitan valores NULL.Additionally, clustered indexes benefit from being created on unique or nonnull columns.

  • Las columnas de los tipos de datos ntext, text, image, varchar(max) , nvarchar(max) y varbinary(max) no pueden especificarse como columnas de clave 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. En cambio, los tipos de datos varchar(max) , nvarchar(max) , varbinary(max) y xml pueden participar en un índice no agrupado como columnas de índice sin clave.However, varchar(max), nvarchar(max), varbinary(max), and xml data types can participate in a nonclustered index as nonkey index columns. Para obtener más información, vea la sección 'Índice con columnas incluidas' en esta guía.For more information, see the section 'Index with Included Columns' in this guide.

  • El tipo de datos xml solo puede ser una columna de clave en un índice XML.An xml data type can only be a key column only in an XML index. Para obtener más información, consulte Índices XML (SQL Server).For more information, see XML Indexes (SQL Server). SQL Server 2012 SP1 presenta un nuevo tipo de índice XML denominado índice XML selectivo.SQL Server 2012 SP1 introduces a new type of XML index known as a Selective XML Index. Este nuevo índice puede mejorar el rendimiento de las consultas en datos almacenados como XML en SQL Server, lo que permitirá indizar mucho más rápidamente grandes cargas de trabajo de datos XML y mejorar la escalabilidad reduciendo los costos de almacenamiento del propio í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 obtener más información, consulte Índices XML selectivos (SXI).For more information, see Selective XML Indexes (SXI).

  • Examine la unicidad de las columnas.Examine column uniqueness. Un índice único en lugar de un índice no único con la misma combinación de columnas proporciona información adicional al optimizador de consultas y, por tanto, resulta más ú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 obtener más información, vea Directrices para diseñar índices únicos en esta guía.For more information, see Unique Index Design Guidelines in this guide.

  • Examine la distribución de los datos en la columna.Examine data distribution in the column. A menudo, se crean consultas cuya ejecución es muy larga al indizar una columna con pocos valores únicos, o bien al realizar una combinación en dicha columna.Frequently, a long-running query is caused by indexing a column with few unique values, or by performing a join on such a column. Se trata de un problema fundamental con los datos y la consulta, y normalmente no se puede resolver sin identificar esta situación.This is a fundamental problem with the data and query, and generally cannot be resolved without identifying this situation. Por ejemplo, una agenda telefónica ordenada por apellidos no localizará rápidamente a una persona si todas las personas de la ciudad se llaman Smith o 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 obtener más información acerca de la distribución de datos, vea Statistics.For more information about data distribution, see Statistics.

  • Considere la posibilidad de usar índices filtrados en columnas que tengan subconjuntos bien definidos, por ejemplo columnas dispersas, columnas con una mayoría de valores NULL, columnas con categorías de valores y columnas con intervalos de valores diferenciados.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. Un índice filtrado bien diseñado puede mejorar el rendimiento de las consultas, así como reducir los costos de almacenamiento y de mantenimiento de los índices.A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce storage costs.

  • Tenga en cuenta el orden de las columnas si el índice va a contener varias columnas.Consider the order of the columns if the index will contain multiple columns. La columna que se usa en la cláusula WHERE en una condición de búsqueda igual a (=), mayor que (>), menor que (<) o BETWEEN, o que participa en una combinación, debe situarse en primer lugar.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. Las demás columnas deben ordenarse basándose en su nivel de diferenciación, es decir, de más distintas a menos distintas.Additional columns should be ordered based on their level of distinctness, that is, from the most distinct to the least distinct.

    Por ejemplo, si el índice se define como LastName, FirstName , resultará útil si el criterio de búsqueda es WHERE LastName = 'Smith' o 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%'. Sin embargo, el optimizador de consultas no utilizará el índice en una consulta que solo busque FirstName (WHERE FirstName = 'Jane').However, the query optimizer would not use the index for a query that searched only on FirstName (WHERE FirstName = 'Jane').

  • Tenga en cuenta la indización de columnas calculadas.Consider indexing computed columns. Para obtener más información, vea Indexes on Computed Columns.For more information, see Indexes on Computed Columns.

Características de los índicesIndex Characteristics

Después de determinar que un índice resulta adecuado para una consulta, puede seleccionar el tipo de índice que mejor se ajusta a la situación.After you have determined that an index is appropriate for a query, you can select the type of index that best fits your situation. Entre las características de los índices se incluyen:Index characteristics include the following:

  • Índices agrupados y no agrupadosClustered versus nonclustered
  • Índices exclusivos y no exclusivosUnique versus nonunique
  • Índices de una sola columna y de varias columnasSingle column versus multicolumn
  • Orden ascendente o descendente en las columnas del índiceAscending or descending order on the columns in the index
  • Índices de tabla completa y filtrados en índices no clústerFull-table versus filtered for nonclustered indexes
  • Almacén de columnas y almacén de filasColumnstore versus rowstore
  • Índice de hash e índice no agrupado para tablas optimizadas para memoriaHash versus nonclustered for Memory-Optimized tables

También puede personalizar las características iniciales de almacenamiento del índice para optimizar su rendimiento o mantenimiento; por ejemplo, puede establecer una opción 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. Además, puede determinar la ubicación de almacenamiento del índice si utiliza grupos de archivos o esquemas de partición y mejorar así el rendimiento.Also, you can determine the index storage location by using filegroups or partition schemes to optimize performance.

Colocación de índices en grupos de archivos o esquemas de particionesIndex Placement on Filegroups or Partitions Schemes

Al desarrollar la estrategia de diseño del índice, debe tener en cuenta la ubicación de los índices en los grupos de archivos asociados con la base de datos.As you develop your index design strategy, you should consider the placement of the indexes on the filegroups associated with the database. La selección cuidadosa del grupo de archivos o del esquema de partición puede mejorar el rendimiento de la consulta.Careful selection of the filegroup or partition scheme can improve query performance.

De forma predeterminada, los índices se almacenan en el mismo grupo de archivos que la tabla base en la que se crea el índice.By default, indexes are stored in the same filegroup as the base table on which the index is created. Un índice clúster sin particiones y la tabla base siempre residen en el mismo grupo de archivos.A nonpartitioned clustered index and the base table always reside in the same filegroup. Sin embargo, puede hacer lo siguiente:However, you can do the following:

  • Crear índices no clúster en un grupo de archivos diferente del grupo de archivos de la tabla base o el índice clúster.Create nonclustered indexes on a filegroup other than the filegroup of the base table or clustered index.
  • Crear particiones de índices clúster y no clúster repartidos en varios grupos de archivos.Partition clustered and nonclustered indexes to span multiple filegroups.
  • Mover una tabla de un grupo de archivos a otro quitando el índice clúster y especificando un nuevo grupo de archivos o un esquema de partición en la cláusula MOVE TO de la instrucción DROP INDEX o utilizando la instrucción CREATE INDEX con la 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.

Si se crea el índice no clúster en otro grupo de archivos, es posible mejorar el rendimiento si los grupos de archivos utilizan distintas unidades físicas con sus propios 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. De ese modo, la información de índice y los datos pueden leerse en paralelo mediante varios cabezales de disco.Data and index information can then be read in parallel by the multiple disk heads. Por ejemplo, si la misma consulta emplea Table_A del grupo de archivos f1 e Index_A del grupo de archivos f2 , se puede mejorar el rendimiento porque ambos grupos de archivos se están usando sin contención.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. Sin embargo, si la consulta examina Table_A pero no se hace referencia a Index_A , solo se usará el grupo de archivos f1 .However, if Table_A is scanned by the query but Index_A is not referenced, only filegroup f1 is used. Esto no produce ninguna mejora de rendimiento.This creates no performance gain.

Como no se puede predecir qué tipo de acceso tendrá lugar ni cuándo, la mejor decisión consiste en repartir las tablas e índices en todos los grupos de archivos.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. De este modo se garantiza el acceso a todos los discos, ya que todos los datos e índices están repartidos por igual entre todos los discos independientemente de la forma de acceso a los datos.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. También se trata de un método más sencillo para los administradores de sistemas.This is also a simpler approach for system administrators.

Particiones entre varios grupos de archivosPartitions across multiple Filegroups

También puede considerar la opción de crear particiones de clúster y no clúster en varios grupos de archivos.You can also consider partitioning clustered and nonclustered indexes across multiple filegroups. Los índices con particiones se dividen horizontalmente o por filas, basándose en una función de partición.Partitioned indexes are partitioned horizontally, or by row, based on a partition function. La función de partición define cómo se asigna cada fila a un conjunto de particiones en los valores de ciertas columnas, denominadas columnas de partición.The partition function defines how each row is mapped to a set of partitions based on the values of certain columns, called partitioning columns. Un esquema de partición especifica la asignación de las particiones a un conjunto de grupos de archivos.A partition scheme specifies the mapping of the partitions to a set of filegroups.

La creación de particiones de un índice puede proporcionar las siguientes ventajas:Partitioning an index can provide the following benefits:

  • Proporcionar sistemas escalables que hacen que los índices grandes sean más fáciles de administrar.Provide scalable systems that make large indexes more manageable. Los sistemas OLTP, por ejemplo, pueden implementar aplicaciones orientadas a particiones que gestionan índices grandes.OLTP systems, for example, can implement partition-aware applications that deal with large indexes.

  • Realizar consultas de forma más rápida y eficiente.Make queries run faster and more efficiently. Cuando las consultas tienen acceso a varias particiones de un índice, el optimizador de consultas puede procesar particiones individuales simultáneamente y excluir particiones que no están afectadas por la 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 obtener más información, vea Partitioned Tables and Indexes.For more information, see Partitioned Tables and Indexes.

Directrices para diseñar el criterio de ordenación de los índicesIndex Sort Order Design Guidelines

Al definir índices, debe tenerse en cuenta si los datos de la columna de clave de índice se almacenan en orden ascendente o descendente.When defining indexes, you should consider whether the data for the index key column should be stored in ascending or descending order. El orden ascendente es el predeterminado y mantiene la compatibilidad con las versiones anteriores de SQL ServerSQL Server.Ascending is the default and maintains compatibility with earlier versions of SQL ServerSQL Server. La sintaxis de las instrucciones CREATE INDEX, CREATE TABLE y ALTER TABLE admite las palabras clave ASC (ascendente) y DESC (descendente) en columnas individuales de índices y restricciones.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.

La especificación del orden en que se almacenan los valores de clave en un índice es de utilidad cuando las consultas que hacen referencia a la tabla tienen cláusulas ORDER BY que especifican distintas direcciones para las columnas de clave del í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. En estos casos, el índice puede eliminar la necesidad de un operador SORT en el plan de consultas, lo que aumenta la eficacia de la consulta.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 ejemplo, los compradores del departamento de compras de Adventure Works CyclesAdventure Works Cycles tienen que evaluar la calidad de los productos que compran a los proveedores.For example, the buyers in the Adventure Works CyclesAdventure Works Cycles purchasing department have to evaluate the quality of products they purchase from vendors. Los compradores están especialmente interesados en buscar productos enviados por estos proveedores con una tasa alta de rechazos.The buyers are most interested in finding products sent by these vendors with a high rejection rate. Como se muestra en la siguiente consulta, para recuperar los datos que cumplen estos criterios es necesario organizar la columna RejectedQty de la tabla Purchasing.PurchaseOrderDetail en orden descendente (de mayor a menor) y la columna ProductID en orden ascendente (de menor a mayor).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;  

El siguiente plan de ejecución para esta consulta muestra que el optimizador de consultas utilizó un operador SORT para devolver el conjunto de resultados en el orden especificado mediante la 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

Si se crea un índice con columnas de clave que coincidan con las de la cláusula ORDER BY de la consulta, se puede eliminar el operador SORT del plan de consultas y éste resulta más 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);  

Cuando se ejecuta de nuevo la consulta, el plan de consultas siguiente muestra que se ha eliminado el operador SORT y se utiliza el índice no clúster que se acaba de crear.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

Motor de base de datosDatabase Engine puede moverse con la misma eficacia en cualquier dirección.The Motor de base de datosDatabase Engine can move equally efficiently in either direction. Un índice definido como (RejectedQty DESC, ProductID ASC) se puede seguir utilizando para una consulta en la que se invierte la dirección de ordenación de las columnas en la cláusula ORDER BY.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 ejemplo, una consulta con la cláusula ORDER BY ORDER BY RejectedQty ASC, ProductID DESC puede utilizar el índice.For example, a query with the ORDER BY clause ORDER BY RejectedQty ASC, ProductID DESC can use the index.

Solo se pueden especificar criterios de ordenación para columnas de clave.Sort order can be specified only for key columns. La vista de catálogo sys.index_columns y la función INDEXKEY_PROPERTY informan de si una columna de índice está almacenada en orden ascendente o descendente.The sys.index_columns catalog view and the INDEXKEY_PROPERTY function report whether an index column is stored in ascending or descending order.

MetadatosMetadata

Use estas vistas de metadatos para ver los atributos de los índices.Use these metadata views to see attributes of indexes. Puede obtener más información sobre la arquitectura en algunas de estas vistas.More architectural information is embedded in some of these views.

Nota

En el caso de los índices de almacén de columnas, todas las columnas se almacenan en los metadatos como columnas incluidas.For columnstore indexes, all columns are stored in the metadata as included columns. El índice de almacén de columnas no tiene columnas de clave.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)

Directrices para diseñar índices clústerClustered Index Design Guidelines

Los índices clúster ordenan y almacenan las filas de los datos de la tabla de acuerdo con los valores de la clave del índice.Clustered indexes sort and store the data rows in the table based on their key values. Solo puede haber un índice clúster por cada tabla, porque las filas de datos solo pueden estar ordenadas de una forma.There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. Salvo excepciones, todas las tablas deben incluir un índice clúster definido en las columnas que presenten las siguientes características:With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following:

  • Se pueden utilizar en consultas frecuentes.Can be used for frequently used queries.

  • Proporcionan un alto grado de unicidad.Provide a high degree of uniqueness.

    Nota

    Cuando crea una restricción PRIMARY KEY, se crea automáticamente un índice único en las columnas.When you create a PRIMARY KEY constraint, a unique index on the column, or columns, is automatically created. De forma predeterminada, este índice es clúster; sin embargo, puede especificar un índice no clúster cuando crea la restricción.By default, this index is clustered; however, you can specify a nonclustered index when you create the constraint.

  • Se pueden utilizar en consultas por rango.Can be used in range queries.

Si el índice agrupado no se crea con la propiedad UNIQUE, el Motor de base de datosDatabase Engine agrega automáticamente a la tabla una columna de valor de unicidad de 4 bytes.If the clustered index is not created with the UNIQUE property, the Motor de base de datosDatabase Engine automatically adds a 4-byte uniqueifier column to the table. Cuando es necesario, el Motor de base de datosDatabase Engine agrega automáticamente un valor de unicidad a una fila para hacer que cada clave sea única.When it is required, the Motor de base de datosDatabase Engine automatically adds a uniqueifier value to a row to make each key unique. Esta columna y sus valores se utilizan de forma interna; los usuarios no pueden verlos ni tener acceso a ellos.This column and its values are used internally and cannot be seen or accessed by users.

Arquitectura de los índices clústerClustered Index Architecture

En SQL ServerSQL Server, los índices se organizan como árboles B.In SQL ServerSQL Server, indexes are organized as B-Trees. Las páginas de un árbol b de índice se llaman nodos del índice.Each page in an index B-tree is called an index node. El nodo superior del árbol b se llama nodo raíz.The top node of the B-tree is called the root node. Los nodos inferiores del índice se denominan nodos hoja.The bottom nodes in the index are called the leaf nodes. Los niveles del índice entre el nodo raíz y los nodos hoja se conocen en conjunto como niveles intermedios.Any index levels between the root and the leaf nodes are collectively known as intermediate levels. En un índice clúster, los nodos hoja contienen las páginas de datos de la tabla subyacente.In a clustered index, the leaf nodes contain the data pages of the underlying table. El nodo raíz y los nodos intermedios incluyen páginas de índice que contienen filas de índice.The root and intermediate level nodes contain index pages holding index rows. Cada fila de índice contiene un valor clave y un puntero a una página de nivel intermedio en el árbol b, o bien a una fila de datos del nivel hoja del í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. Las páginas de cada nivel del índice se vinculan en una lista con vínculos dobles.The pages in each level of the index are linked in a doubly-linked list.

Los índices clúster tienen una fila en sys.partitions, con index_id = 1 para cada partición usada por el índice.Clustered indexes have one row in sys.partitions, with index_id = 1 for each partition used by the index. De forma predeterminada, un índice clúster tiene una sola partición.By default, a clustered index has a single partition. Cuando un índice clúster tiene múltiples particiones, cada partición tiene una estructura de árbol b que contiene los datos de esa partición específica.When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition. Por ejemplo, si un índice clúster tiene cuatro particiones, hay cuatro estructuras de árbol b, una en cada partición.For example, if a clustered index has four partitions, there are four B-tree structures; one in each partition.

En función de los tipos de datos del índice clúster, cada estructura de índice clúster tendrá una o más unidades de asignación en las que almacenar y administrar los datos de una partición específica.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. Como mínimo, cada índice clúster tendrá una unidad de asignación IN_ROW_DATA por partición.At a minimum, each clustered index will have one IN_ROW_DATA allocation unit per partition. El índice agrupado también tendrá una unidad de asignación LOB_DATA por partición si contiene columnas de objetos grandes (LOB).The clustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns. También tendrá una unidad de asignación ROW_OVERFLOW_DATA por partición si contiene columnas de longitud variable que superen el límite de tamaño de fila de 8060 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.

Las páginas de la cadena de datos y las filas que contienen se ordenan según el valor de la clave de índice clúster.The pages in the data chain and the rows in them are ordered on the value of the clustered index key. Todas las inserciones se hacen en el punto en el que el valor de clave de la fila insertada quede dentro de la secuencia de orden entre las filas existentes.All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows.

En esta ilustración se muestra la estructura de un índice clúster en una sola partición.This illustration shows the structure of a clustered index in a single partition.

bokind2

Consideraciones sobre consultasQuery Considerations

Antes de crear índices clúster, debe conocer cómo se tiene acceso a los datos.Before you create clustered indexes, understand how your data will be accessed. Considere que utiliza un índice clúster en consultas que realizan lo siguiente:Consider using a clustered index for queries that do the following:

  • Devuelven un intervalo de valores mediante el uso de operadores como BETWEEN, >, >=, < y <=.Return a range of values by using operators such as BETWEEN, >, >=, <, and <=.

    Cuando la fila se encuentra con el primer valor mediante el índice clúster, se garantiza que las filas con los valores indizados posteriores son físicamente adyacentes.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 ejemplo, si una consulta recupera registros entre un intervalo de números de pedidos de ventas, un índice clúster en la columna SalesOrderNumber puede encontrar rápidamente la fila que contiene el número de pedido de ventas inicial y, a continuación, recuperar todas las filas sucesivas de la tabla hasta llegar al último número de pedido de ventas.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.

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

  • Usan cláusulas JOIN; por lo general, son columnas de clave externa.Use JOIN clauses; typically these are foreign key columns.

  • Usan cláusulas ORDER BY o GROUP BY.Use ORDER BY or GROUP BY clauses.

    Un índice en las columnas especificadas en la cláusula ORDER BY o GROUP BY puede eliminar la necesidad de que Motor de base de datosDatabase Engine ordene los datos, puesto que las filas ya están ordenadas.An index on the columns specified in the ORDER BY or GROUP BY clause may remove the need for the Motor de base de datosDatabase Engine to sort the data, because the rows are already sorted. De ese modo, el rendimiento de las consultas aumenta.This improves query performance.

Consideraciones sobre columnasColumn Considerations

Por regla genera, debe definir la clave de índice clúster con el menor número de columnas posible.Generally, you should define the clustered index key with as few columns as possible. Considere columnas que cuentan con uno o varios de los siguientes atributos:Consider columns that have one or more of the following attributes:

  • Son únicas o contienen muchos valores distintosAre unique or contain many distinct values

    Por ejemplo, un Id. de empleado identifica de forma exclusiva a los empleados.For example, an employee ID uniquely identifies employees. Un índice agrupado o una restricción PRIMARY KEY en la columna EmployeeID aumentaría el rendimiento de las consultas que buscan información de empleado según el número de identificador del empleado.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. También se podría crear un índice clúster en las columnas LastName, FirstNamey MiddleName , ya que los registros de empleados se suelen agrupar y consultar de esta forma, y la combinación de estas columnas seguiría proporcionando un alto grado de diferencia.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.

    Sugerencia

    Si no se especifica de otra forma, al crear una restricción PRIMARY KEY, SQL ServerSQL Server crea un índice agrupado para admitir esa restricción.If not specified differently, when creating a PRIMARY KEY constraint, SQL ServerSQL Server creates a clustered index to support that constraint. Aunque se puede usar uniqueidentifier para aplicar la unicidad como PRIMARY KEY, no es una clave de agrupación eficaz.Although a uniqueidentifier can be used to enforce uniqueness as a PRIMARY KEY, it is not an efficient clustering key. Si usa uniqueidentifier como PRIMARY KEY, se recomienda crearlo como un índice no agrupado y usar otra columna, como IDENTITY, para crear el índice agrupado.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.

  • Se tiene acceso a ellas de forma secuencialAre accessed sequentially

    Por ejemplo, un identificador de producto identifica de forma exclusiva los productos de la tabla Production.Product en la base de datos AdventureWorks2012AdventureWorks2012 .For example, a product ID uniquely identifies products in the Production.Product table in the AdventureWorks2012AdventureWorks2012 database. Un índice clúster en WHERE ProductID BETWEEN 980 and 999mejoraría las consultas en las que se especifica una búsqueda secuencial, como 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. Esto se debe a que las filas se almacenan de forma ordenada en esta columna de clave.This is because the rows would be stored in sorted order on that key column.

  • Definido como IDENTITY.Defined as IDENTITY.

  • Se utilizan con frecuencia para ordenar los datos recuperados de una tablaUsed frequently to sort the data retrieved from a table.

    Puede resultar conveniente agrupar, es decir, ordenar físicamente la tabla de dicha columna para evitar una operación de ordenación cada vez que se consulta la columna.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.

Los índices clúster no son adecuados para los siguientes atributos:Clustered indexes are not a good choice for the following attributes:

  • Columnas sometidas a cambios frecuentesColumns that undergo frequent changes

    Esto provoca que se mueva toda la fila, ya que Motor de base de datosDatabase Engine debe mantener los valores de los datos de la fila ordenados físicamente.This causes in the whole row to move, because the Motor de base de datosDatabase Engine must keep the data values of a row in physical order. Esta consideración es importante en sistemas de procesamiento de transacciones de gran volumen en los que los datos tienden a ser volátiles.This is an important consideration in high-volume transaction processing systems in which data is typically volatile.

  • Claves ampliasWide keys

    Las claves amplias se componen de varias columnas o varias columnas de gran tamaño.Wide keys are a composite of several columns or several large-size columns. Los valores clave del índice clúster se utilizan en todos los índices no clúster como claves de búsqueda.The key values from the clustered index are used by all nonclustered indexes as lookup keys. Los índices no clúster definidos en la misma tabla serán bastante más grandes, ya que sus entradas contienen la clave de agrupación en clústeres y las columnas de clave definidas para dicho índice no clúster.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.

Directrices para diseñar índices no clústerNonclustered Index Design Guidelines

Un índice no clúster contiene los valores de clave del índice y localizadores de fila que apuntan a la ubicación de almacenamiento de los datos de tabla.A nonclustered index contains the index key values and row locators that point to the storage location of the table data. Se pueden crear varios índices no clúster en una tabla o una vista indizada.You can create multiple nonclustered indexes on a table or indexed view. Por lo general, se deben diseñar índices no clúster para mejorar el rendimiento de consultas utilizadas con frecuencia no cubiertas por el índice clúster.Generally, nonclustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index.

Al igual que cuando se utiliza un índice de un libro, el optimizador de consultas busca valores de datos en el índice no clúster para encontrar la ubicación del valor de datos en la tabla y, a continuación, recupera los datos directamente de esa ubicación.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. Este sistema convierte a los índices no clúster en la opción más apropiada para las consultas de coincidencia exacta, dado que el índice contiene entradas que describen la ubicación exacta en la tabla de los valores de datos que se buscan en las 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 ejemplo, para consultar la tabla HumanResources. Employee con el fin de ver todos los subordinados de un jefe determinado, el optimizador de consultas podría usar el índice no clúster IX_Employee_ManagerID, que tiene ManagerID como columna de clave.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. El optimizador de consultas puede buscar rápidamente todas las entradas del índice que coinciden con el ManagerIDespecificado.The query optimizer can quickly find all entries in the index that match the specified ManagerID. Cada entrada de índice apunta a la página y fila exactas de la tabla, o índice clúster, en que se pueden encontrar los datos correspondientes.Each index entry points to the exact page and row in the table, or clustered index, in which the corresponding data can be found. Una vez que el optimizador de consultas busca todas las entradas del índice, puede ir directamente a la página y fila exactas para recuperar los datos.After the query optimizer finds all entries in the index, it can go directly to the exact page and row to retrieve the data.

Arquitectura de los índices no clústerNonclustered Index Architecture

Los índices no clúster tienen la misma estructura de árbol b que los índices clúster, excepto por las siguientes diferencias importantes:Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:

  • Las filas de datos de la tabla subyacente no están ordenadas ni almacenadas basándose en sus claves no agrupadas.The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.

  • El nivel hoja de un índice no agrupado está compuesto por páginas de índices, en lugar de páginas de datos.The leaf level of a nonclustered index is made up of index pages instead of data pages.

Los localizadores de filas de las filas de índices no clúster pueden ser un puntero a la fila o una clave de índice clúster para una fila, tal como se describe a continuación: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:

  • Si la tabla es un montón, lo que significa que no tiene ningún índice clúster, el localizador de fila es un puntero a la fila.If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. El puntero se genera a partir del identificador (Id.) de archivo, el número de página y el número de la fila dentro de la página.The pointer is built from the file identifier (ID), page number, and number of the row on the page. El puntero completo se conoce como Id. de fila (RID).The whole pointer is known as a Row ID (RID).

  • Si la tabla tiene un índice clúster o si el índice está en una vista indizada, el localizador de fila es la clave del índice clúster para la fila.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.

Los índices no agrupados tienen una fila en sys.partitions, con index_id > 1 para cada partición usada por el índice.Nonclustered indexes have one row in sys.partitions with index_id > 1 for each partition used by the index. De forma predeterminada, un índice no clúster tiene una sola partición.By default, a nonclustered index has a single partition. Cuando un índice no clúster tiene varias particiones, cada una tiene una estructura de árbol b que contiene las filas de índice de esa partición 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 ejemplo, si un índice no clúster tiene cuatro particiones, habrá cuatro estructuras de árbol b, una en cada partición.For example, if a nonclustered index has four partitions, there are four B-tree structures, with one in each partition.

En función de los tipos de datos del índice no clúster, cada estructura de índice no clúster tendrá una o más unidades de asignación en las que almacenar y administrar los datos de una partición 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. Como mínimo, cada índice no agrupado tendrá una unidad de asignación IN_ROW_DATA por partición encargada de almacenar las páginas de árbol B del índice.At a minimum, each nonclustered index will have one IN_ROW_DATA allocation unit per partition that stores the index B-tree pages. El índice no agrupado también tendrá una unidad de asignación LOB_DATA por partición si contiene columnas de objetos grandes (LOB).The nonclustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns. Además, tendrá una unidad de asignación ROW_OVERFLOW_DATA por partición si contiene columnas de longitud variable que superen el límite de tamaño de fila de 8060 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.

En la siguiente ilustración se muestra la estructura de un índice no clúster en una sola partición.The following illustration shows the structure of a nonclustered index in a single partition.

bokind1a

Consideraciones acerca de la base de datosDatabase Considerations

Tenga en cuenta las características de la base de datos al diseñar índices no clúster.Consider the characteristics of the database when designing nonclustered indexes.

  • Las bases de datos o tablas que exigen pocos requisitos para la actualización, pero suelen contener un gran volumen de datos, se pueden beneficiar de muchos índices no clúster para mejorar el optimizador de consultas.Databases or tables with low update requirements, but large volumes of data can benefit from many nonclustered indexes to improve query performance. Considere la creación de índices filtrados para subconjuntos bien definidos de datos con el fin de mejorar el rendimiento de las consultas, reducir los costos de almacenamiento del índice y reducir los costos de mantenimiento del índice en comparación con índices no clúster de la tabla 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.

    Las aplicaciones y bases de datos del sistema de ayuda para la toma de decisiones que contienen principalmente datos de solo lectura se pueden beneficiar de muchos índices no clúster.Decision Support System applications and databases that contain primarily read-only data can benefit from many nonclustered indexes. El optimizador de consultas tiene más índices entre los que elegir para determinar el método de acceso más rápido. Además, las características que exigen pocos requisitos para la actualización de la base de datos se traducen en que el mantenimiento de los índices no reducirá el rendimiento.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.

  • Las aplicaciones y bases de datos de procesamiento de transacciones en línea (OLTP) que contienen tablas deben evitar el exceso de índices.Online Transaction Processing (OLTP) applications and databases that contain heavily updated tables should avoid over-indexing. Además, los índices deben ser estrechos, es decir, con la menor cantidad de columnas posible.Additionally, indexes should be narrow, that is, with as few columns as possible.

    Si se utiliza un gran número de índices en una tabla, el rendimiento de las instrucciones INSERT, UPDATE, DELETE y MERGE se verá afectado, ya que todos los índices deben ajustarse adecuadamente a medida que cambian los datos de la tabla.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.

Consideraciones sobre consultasQuery Considerations

Antes de crear índices no clúster, debe conocer cómo se tiene acceso a los datos.Before you create nonclustered indexes, you should understand how your data will be accessed. Considere la posibilidad de utilizar un índice no clúster para consultas que cuentan con los siguientes atributos:Consider using a nonclustered index for queries that have the following attributes:

  • Usan cláusulas JOIN o GROUP BY.Use JOIN or GROUP BY clauses.

    Crean varios índices no clúster para las columnas que intervienen en operaciones de combinación y de agrupación, y un índice clúster para las columnas de clave externa.Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.

  • No devuelven conjuntos de resultados de gran tamaño.Queries that do not return large result sets.

    Cree índices filtrados para atender consultas que devuelven un subconjunto bien definido de filas en una tabla grande.Create filtered indexes to cover queries that return a well-defined subset of rows from a large table.

    Sugerencia

    Por lo general, la cláusula WHERE de la instrucción CREATE INDEX coincide con la cláusula WHERE de una consulta que se cubre.Typically the WHERE clause of the CREATE INDEX statement matches the WHERE clause of a query being covered.

  • Contienen columnas que suelen incluirse en las condiciones de búsqueda de una consulta, como la cláusula WHERE, que devuelven coincidencias exactas.Contain columns frequently involved in search conditions of a query, such as WHERE clause, that return exact matches.

    Sugerencia

    Cuando agregue índices nuevos, considere la relación costo-beneficio.Consider the cost versus benefit when adding new indexes. Quizás sea preferible consolidar las necesidades de consultas adicionales en un índice existente.It may be preferable to consolidate additional query needs into an existing index. Por ejemplo, considere agregar una o dos columnas de nivel hoja adicionales a un índice existente, si permite la cobertura de varias consultas críticas en lugar de tener exactamente un índice de cobertura por cada 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.

Consideraciones sobre columnasColumn Considerations

Tenga en cuenta las columnas que tengan uno o varios de estos atributos:Consider columns that have one or more of these attributes:

  • Cubren la consulta.Cover the query.

    Se obtienen mejoras de rendimiento cuando el índice contiene todas las columnas de la consulta.Performance gains are achieved when the index contains all columns in the query. El optimizador de consultas puede buscar todos los valores de columna del índice; no se tiene acceso a los datos de tabla o de índice clúster, lo que se traduce en menos operaciones de E/S de disco.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. Utilice un índice con columnas incluidas para agregar columnas de cobertura en lugar de crear una clave de índice ancho.Use index with included columns to add covering columns instead of creating a wide index key.

    Si la tabla tiene un índice clúster, las columnas definidas en él se anexan automáticamente al final de cada índice no clúster de la tabla.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. Esto puede producir una consulta cubierta sin especificar las columnas de índice clúster en la definición del índice no clúster.This can produce a covered query without specifying the clustered index columns in the definition of the nonclustered index. Por ejemplo, si una tabla tiene un índice clúster en la columna C, un índice no clúster en las columnas B y A tendrá como columnas de valores de clave las columnas B, Ay 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.

  • Gran número de valores distintos, como combinaciones de nombres y apellidos, si se utiliza un índice clúster para otras columnas.Lots of distinct values, such as a combination of last name and first name, if a clustered index is used for other columns.

    Si hay muy pocos valores distintos, como solo 1 y 0, la mayoría de las consultas no utilizarán el índice, ya que una exploración de la tabla suele ser más 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 este tipo de datos, considere la creación de un índice filtrado en un valor distintivo que solo se da en un número pequeño de filas.For this type of data, consider creating a filtered index on a distinct value that only occurs in a small number of rows. Por ejemplo, si la mayoría de los valores es 0, el optimizador de consultas puede utilizar un índice filtrado para las filas de datos que contienen 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 columnas incluidas para ampliar índices no clústerUse Included Columns to Extend Nonclustered Indexes

Puede ampliar la funcionalidad de índices no clúster agregando columnas sin clave en el nivel hoja del índice no clúster.You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. Al incluir columnas sin clave, puede crear índices no clúster que abarcan más consultas.By including nonkey columns, you can create nonclustered indexes that cover more queries. Esto se debe a que las columnas sin clave tienen las siguientes ventajas:This is because the nonkey columns have the following benefits:

  • Pueden ser tipos de datos que no están permitidos como columnas de clave de índice.They can be data types not allowed as index key columns.

  • El Motor de base de datosDatabase Engine no las tiene en cuenta cuando calcula el número de columnas de clave de índice o el tamaño de clave de índice.They are not considered by the Motor de base de datosDatabase Engine when calculating the number of index key columns or index key size.

Un índice con columnas sin clave incluidas puede mejorar significativamente el rendimiento de una consulta cuando todas las columnas de la consulta se incluyen como columnas de clave o columnas sin clave.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. Las mejoras en el rendimiento se consiguen porque el optimizador de consultas puede localizar todos los valores de las columnas del índice, sin tener acceso a los datos de la tabla o del índice clúster, lo que da como resultado menos operaciones 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.

Nota

Cuando un índice contiene todas las columnas a las que hace referencia la consulta, normalmente se dice que abarca la consulta.When an index contains all the columns referenced by the query it is typically referred to as covering the query.

Las columnas de clave se almacenan en todos los niveles del índice, mientras que las columnas sin clave solo se almacenan en el nivel hoja.While key columns are stored at all levels of the index, nonkey columns are stored only at the leaf level.

Usar columnas incluidas para evitar límites de tamañoUsing Included Columns to Avoid Size Limits

Puede incluir columnas sin clave en un índice no clúster para evitar que supere las limitaciones actuales de tamaño del índice de un máximo de 16 columnas de clave y un tamaño máximo de las claves 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. El Motor de base de datosDatabase Engine no tiene en cuenta las columnas sin clave al calcular el número de columnas de clave de índice o el tamaño de clave de índice.The Motor de base de datosDatabase Engine does not consider nonkey columns when calculating the number of index key columns or index key size.
Por ejemplo, suponga que desea indizar las siguientes columnas de la tabla 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)

Puesto que los tipos de datos nchar y nvarchar necesitan 2 bytes para cada carácter, un índice que contenga estas tres columnas superaría la limitación de tamaño de 900 bytes en 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). Al utilizar la cláusula INCLUDE de la instrucción CREATE INDEX , la clave de índice se puede definir como (Title, Revision) y FileName como columna sin clave.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. De esta forma, el tamaño de las claves de índice sería de 110 bytes (55 * 2) y el índice seguiría conteniendo todas las columnas necesarias.In this way, the index key size would be 110 bytes (55 * 2), and the index would still contain all the required columns. La siguiente instrucción crea ese índice.The following statement creates such an index.

CREATE INDEX IX_Document_Title   
ON Production.Document (Title, Revision)   
INCLUDE (FileName);   
Directrices del índice con columnas incluidasIndex with Included Columns Guidelines

Cuando diseñe índices no clúster con columnas incluidas tenga en cuenta las siguientes directrices:When you design nonclustered indexes with included columns consider the following guidelines:

  • Las columnas sin clave se definen en la cláusula INCLUDE de la instrucción CREATE INDEX.Nonkey columns are defined in the INCLUDE clause of the CREATE INDEX statement.

  • Las columnas sin clave solo se pueden definir en índices no clúster en tablas o vistas indizadas.Nonkey columns can only be defined on nonclustered indexes on tables or indexed views.

  • Se admiten todos los tipos de datos, a excepción de text, ntexte image.All data types are allowed except text, ntext, and image.

  • Las columnas calculadas que son deterministas, y precisas o imprecisas, pueden ser columnas incluidas.Computed columns that are deterministic and either precise or imprecise can be included columns. Para obtener más información, vea Indexes on Computed Columns.For more information, see Indexes on Computed Columns.

  • Al igual que con las columnas de clave, las columnas calculadas derivadas de los tipos de datos image, ntexty text pueden ser columnas sin clave (incluidas) siempre que se permita el tipo de datos de la columna calculada como columna de índice sin clave.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.

  • Los nombres de columna no se pueden especificar en la lista INCLUDE y en la lista de columnas de clave.Column names cannot be specified in both the INCLUDE list and in the key column list.

  • Los nombres de columna no se pueden repetir en la lista INCLUDE.Column names cannot be repeated in the INCLUDE list.

Directrices del tamaño de columnasColumn Size Guidelines
  • Es necesario definir como mínimo una columna de clave.At least one key column must be defined. El número máximo de columnas sin clave es de 1023 columnas.The maximum number of nonkey columns is 1023 columns. Éste es el número máximo de columnas de la tabla menos 1.This is the maximum number of table columns minus 1.

  • Las columnas de clave de índice, excluyendo las sin clave, deben seguir las restricciones de tamaño de índice existentes de 16 columnas de clave como máximo y un tamaño de las claves de índice total de 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.

  • El tamaño total de todas las columnas sin clave solo está limitado por el tamaño de las columnas especificadas en la cláusula INCLUDE; por ejemplo, las columnas varchar(max) están 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.

Directrices para modificar columnasColumn Modification Guidelines

Cuando se modifica una columna de tabla que se ha definido como una columna incluida, se aplican las siguientes restricciones:When you modify a table column that has been defined as an included column, the following restrictions apply:

  • Las columnas sin clave no se pueden quitar de la tabla, a menos que antes se quite el índice.Nonkey columns cannot be dropped from the table unless the index is dropped first.

  • Las columnas sin clave no se pueden cambiar, excepto para hacer lo siguiente:Nonkey columns cannot be changed, except to do the following:

    • Cambiar la nulabilidad de NOT NULL a NULL.Change the nullability of the column from NOT NULL to NULL.

    • Aumentar la longitud de las columnas varchar, nvarcharo varbinary .Increase the length of varchar, nvarchar, or varbinary columns.

      Nota

      También se aplican restricciones de modificación a las columnas de clave de índice.These column modification restrictions also apply to index key columns.

Recomendaciones de diseñoDesign Recommendations

Rediseñe índices no clúster con un tamaño de las claves de índice grande para que solo las columnas utilizadas para búsquedas sean columnas de clave.Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Haga que todas las demás columnas que abarcan la consulta sean columnas sin clave incluidas.Make all other columns that cover the query included nonkey columns. De esta forma, tendrá todas las columnas necesarias para abarcar la consulta pero la clave de índice en sí será pequeña y eficaz.In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.

Por ejemplo, suponga que desea diseñar un índice para abarcar la siguiente consulta.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 abarcar la consulta, cada columna debe definirse en el índice.To cover the query, each column must be defined in the index. Aunque puede definir todas las columnas como columnas de clave, el tamaño de clave debe ser de 334 bytes.Although you could define all columns as key columns, the key size would be 334 bytes. Como la única columna que se usa de verdad como criterio de búsqueda es la columna PostalCode , que tiene una longitud de 30 bytes, un mejor diseño del índice definiría PostalCode como columna de clave e incluiría todas las demás columnas como columnas sin clave.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.

La siguiente instrucción crea un índice con columnas incluidas para abarcar la 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);  
Consideraciones de rendimientoPerformance Considerations

Evite agregar columnas que no sean necesarias.Avoid adding unnecessary columns. El hecho de agregar demasiadas columnas de índice, con o sin clave, puede tener las siguientes consecuencias en el rendimiento:Adding too many index columns, key or nonkey, can have the following performance implications:

  • Cabrán menos filas de índice en una página.Fewer index rows will fit on a page. Esto puede crear incrementos de E/S y una reducción de la eficacia de la caché.This could create I/O increases and reduced cache efficiency.

  • Se necesitará más espacio en disco para almacenar el índice.More disk space will be required to store the index. En concreto, al agregar los tipos de datos varchar(max) , nvarchar(max) , varbinary(max) o xml como columnas de índice sin clave, se pueden aumentar significativamente los requisitos de espacio en disco.In particular, adding varchar(max), nvarchar(max), varbinary(max), or xml data types as nonkey index columns may significantly increase disk space requirements. Esto se debe a que los valores de columnas se copian en el nivel hoja del índice.This is because the column values are copied into the index leaf level. Por lo tanto, residen en el índice y en la tabla base.Therefore, they reside in both the index and the base table.

  • Puede que el mantenimiento del índice haga aumentar el tiempo necesario para realizar operaciones de modificación, inserción, actualización o eliminación en la tabla subyacente o la vista indizada.Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.

Debe determinar si la mejora del rendimiento de las consultas compensa el efecto en el rendimiento durante la modificación de datos y en los requisitos de espacio en disco adicionales.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.

Directrices para diseñar índices únicosUnique Index Design Guidelines

Un índice único garantiza que la clave de índice no contiene valores duplicados y, por tanto, cada fila de la tabla es en cierta forma única.A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Resulta conveniente especificar un índice único solo si los propios datos se caracterizan por ser únicos.Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. Por ejemplo, para asegurarse de que los valores de la columna NationalIDNumber de la tabla HumanResources.Employee son únicos, cuando la clave principal es EmployeeID, cree una restricción UNIQUE en la columna 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. Si el usuario intenta especificar el mismo valor en esa columna para más de un empleado, se mostrará un mensaje de error que impedirá la entrada del valor duplicado.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.

En el caso de índices únicos para varias columnas, el índice asegura que cada combinación de valores de la clave de índice sea única.With multicolumn unique indexes, the index guarantees that each combination of values in the index key is unique. Por ejemplo, si se crea un índice único en una combinación de columnas LastName, FirstNamey MiddleName , dos filas de la tabla no podrán tener la misma combinación de valores para estas columnas.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 los índices clúster como los no clúster pueden ser únicos.Both clustered and nonclustered indexes can be unique. Siempre que los datos de la columna sean únicos, puede crear para la misma tabla un índice clúster único y varios índices clúster no únicos.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.

Entre las ventajas de utilizar índices únicos se incluyen:The benefits of unique indexes include the following:

  • Se garantiza la integridad de los datos de las columnas definidas.Data integrity of the defined columns is ensured.

  • Se proporciona información adicional útil para el optimizador de consultas.Additional information helpful to the query optimizer is provided.

Si se crea una restricción PRIMARY KEY o UNIQUE, se creará automáticamente un índice único en las columnas especificadas.Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns. No existen diferencias significativas entre la creación de una restricción UNIQUE y la creación de un índice único independiente de una restricción.There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. La validación de los datos tiene lugar de la misma manera y el optimizador de consultas no establece diferencias entre un índice único creado por una restricción y uno creado 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. Sin embargo, deberá crear una restricción UNIQUE o PRIMARY KEY en la columna cuando el objetivo sea la integridad de los datos.However, you should create a UNIQUE or PRIMARY KEY constraint on the column when data integrity is the objective. Al hacer esto, el objetivo del índice quedará claro.By doing this the objective of the index will be clear.

ConsideracionesConsiderations

  • Un índice único, una restricción UNIQUE o una restricción PRIMARY KEY no se pueden crear si existen valores de clave duplicados en los datos.A unique index, UNIQUE constraint, or PRIMARY KEY constraint cannot be created if duplicate key values exist in the data.

  • Si los datos son únicos y desea hacer cumplir la exclusividad, la creación de un índice único en lugar de un índice no único en la misma combinación de columnas proporciona información adicional para el optimizador de consultas que puede dar como resultado unos planes de ejecución más eficaces.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. En este caso se recomienda crear un índice único (preferiblemente mediante una restricción UNIQUE).Creating a unique index (preferably by creating a UNIQUE constraint) is recommended in this case.

  • Un índice no clúster único puede incluir columnas sin clave.A unique nonclustered index can contain included nonkey columns. Para obtener más información, vea Índice con columnas incluidas.For more information, see Index with Included Columns.

Directrices generales para diseñar índices filtradosFiltered Index Design Guidelines

Un índice filtrado es un índice no clúster optimizado, especialmente indicado para atender consultas que realizan selecciones a partir un subconjunto bien definido de datos.A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. Utiliza un predicado de filtro para indizar una parte de las filas de la tabla.It uses a filter predicate to index a portion of rows in the table. Un índice filtrado bien diseñado puede mejorar el rendimiento de las consultas y reducir los costos de almacenamiento del índice en relación con los índices de tabla completa, así como los costos de mantenimiento.A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

Se aplica a: desde SQL Server 2008SQL Server 2008 hasta SQL Server 2017SQL Server 2017.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

Los índices filtrados pueden proporcionar las siguientes ventajas respecto a los índices de tabla completa:Filtered indexes can provide the following advantages over full-table indexes:

  • Mejor rendimiento de las consultas y mayor calidad del planImproved query performance and plan quality

    Un índice filtrado bien diseñado mejora el rendimiento de las consultas y la calidad del plan de ejecución porque es menor que un índice no clúster de tabla completa y tiene estadí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. Las estadísticas filtradas son más precisas que las de tabla completa porque corresponden solamente a las filas del índice filtrado.The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

  • Menor costo de mantenimiento de índicesReduced index maintenance costs

    El mantenimiento de un índice se realiza únicamente cuando las instrucciones de lenguaje de manipulación de datos (DML) afectan a los datos en el índice.An index is maintained only when data manipulation language (DML) statements affect the data in the index. Un índice filtrado reduce los costos de mantenimiento del índice en comparación con un índice no clúster de tabla completa, ya que es menor y el mantenimiento se realiza únicamente cuando se ven afectados los datos del índice.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. Se puede disponer de una gran cantidad de índices filtrados, sobre todo cuando contienen datos que raramente se ven afectados.It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. De igual forma, si un índice filtrado contiene únicamente datos que se ven afectados a menudo, el tamaño menor del índice reduce el costo de actualización de las estadí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.

  • Costos reducidos de almacenamiento de índicesReduced index storage costs

    La creación de un índice filtrado puede reducir la cantidad de almacenamiento en disco de índices no clúster, cuando no sea necesario un índice de tabla completa.Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. Puede reemplazar un índice no clúster de tabla completa con varios índices filtrados sin aumentar de forma considerable los requisitos de almacenamiento.You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

Los índices filtrados son útiles cuando las columnas contienen subconjuntos de datos bien definidos a los que las consultas hacen referencia en instrucciones SELECT.Filtered indexes are useful when columns contain well-defined subsets of data that queries reference in SELECT statements. Algunos ejemplos son:Examples are:

  • Columnas dispersas que contienen solamente unos pocos valores distintos de NULL.Sparse columns that contain only a few non-NULL values.

  • Columnas heterogéneas que contienen categorías de datos.Heterogeneous columns that contain categories of data.

  • Columnas que contienen intervalos de valores como cantidad de dinero, hora y fechas.Columns that contain ranges of values such as dollar amounts, time, and dates.

  • Particiones de tablas definidas por lógica de comparación simple para los valores de las columnas.Table partitions that are defined by simple comparison logic for column values.

La reducción de los costos de mantenimiento para los índices filtrados es más apreciable cuando el número de filas del índice es pequeño en relación con un índice de tabla 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. Si el índice filtrado incluye la mayoría de las filas en la tabla, puede resultar más costoso mantenerlo que un índice de tabla completa.If the filtered index includes most of the rows in the table, it could cost more to maintain than a full-table index. En ese caso, debe utilizar un índice de tabla completa en lugar de un índice filtrado.In this case, you should use a full-table index instead of a filtered index.

Los índices filtrados se definen en una tabla y solamente admiten operadores de comparación simples.Filtered indexes are defined on one table and only support simple comparison operators. Cuando necesite una expresión de filtro que haga referencia a varias tablas o que tenga lógica compleja, deberá crear una vista.If you need a filter expression that references multiple tables or has complex logic, you should create a view.

Consideraciones de diseñoDesign Considerations

Para diseñar índices filtrados efectivos, es importante entender qué consultas utiliza la aplicación y cómo se relacionan con los subconjuntos de datos.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. Algunos ejemplos de datos que tienen subconjuntos bien definidos son las columnas con una mayoría de valores NULL, las columnas con categorías de valores heterogéneas y las columnas con intervalos de valores diferenciados.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. Las siguientes consideraciones del diseño proporcionan una variedad de escenarios en los que un índice filtrado puede ofrecer ventajas sobre los índices de tabla completa.The following design considerations give a variety of scenarios for when a filtered index can provide advantages over full-table indexes.

Sugerencia

La definición del índice de almacén de columnas no agrupado admite el uso de una condición de filtrado.The nonclustered columnstore index definition supports using a filtered condition. Para minimizar el impacto de rendimiento que tiene agregar un índice de almacén de columnas a una tabla OLTP, use una condición de filtrado para crear un índice de almacén de columnas no agrupado únicamente en los datos inactivos de la carga de trabajo operativa.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 datosFiltered Indexes for subsets of data

Cuando una columna solamente tiene un número pequeño de valores pertinentes para las consultas, puede crear un índice filtrado en el 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 ejemplo, cuando los valores en una columna son principalmente NULL y la consulta solamente selecciona entre valores distintos de NULL, puede crear un índice filtrado para las filas de datos distintos de 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. El índice resultante será menor y tendrá costos de mantenimiento más reducidos que los de un índice no clúster de tabla completa definido en las mismas columnas de clave.The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.

Por ejemplo, la base de datos AdventureWorks2012 tiene una tabla Production.BillOfMaterials con 2679 filas.For example, the AdventureWorks2012 database has a Production.BillOfMaterials table with 2679 rows. La columna EndDate solo tiene 199 filas que contienen un valor distinto de NULL y las otras 2.480 filas contienen valores NULL.The EndDate column has only 199 rows that contain a non-NULL value and the other 2480 rows contain NULL. El siguiente índice filtrado atenderá consultas que devuelven las columnas definidas en el índice y que seleccionan únicamente filas con un valor distinto de 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  

El índice filtrado FIBillOfMaterialsWithEndDate es válido para la consulta siguiente.The filtered index FIBillOfMaterialsWithEndDate is valid for the following query. Puede mostrar el plan de ejecución de consultas para determinar si el optimizador de consultas ha utilizado el í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 obtener más información sobre cómo crear índices filtrados y cómo definir la expresión de predicado del índice filtrado, vea 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 datos heterogéneosFiltered Indexes for heterogeneous data

Cuando una tabla tiene filas de datos heterogéneos, se puede crear un índice filtrado para una o varias categorías de datos.When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data.

Por ejemplo, cada uno de los productos de la tabla Production.Product está asignado a un ProductSubcategoryIDque, a su vez, está asociado a las categorías de producto Bikes, Components, Clothing o 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. Estas categorías son heterogéneas porque sus valores de columna en la tabla Production.Product no están suficientemente correlacionados.These categories are heterogeneous because their column values in the Production.Product table are not closely correlated. Por ejemplo, las columnas Color, ReorderPoint, ListPrice, Weight, Classy Style tienen características únicas para cada categoría de producto.For example, the columns Color, ReorderPoint, ListPrice, Weight, Class, and Style have unique characteristics for each product category. Suponga que se realizan consultas frecuentes de accesorios cuyas subcategorías están comprendidas entre 27 y 36 inclusive.Suppose that there are frequent queries for accessories which have subcategories between 27 and 36 inclusive. Puede mejorar el rendimiento de las consultas de accesorios si crea un índice filtrado en las subcategorías de accesorios como se muestra en el ejemplo siguiente.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;  

El índice filtrado FIProductAccessories abarca la consulta siguiente porque los resultados de las consultasThe filtered index FIProductAccessories covers the following query because the query

se incluyen en el índice y el plan de consulta no incluye búsquedas en una tabla base.results are contained in the index and the query plan does not include a base table lookup. Por ejemplo, la expresión de predicado de la consulta ProductSubcategoryID = 33 es un subconjunto del predicado del índice filtrado ProductSubcategoryID >= 27 y ProductSubcategoryID <= 36, las columnas ProductSubcategoryID y ListPrice del predicado de la consulta son ambas columnas de clave del índice, y el nombre se almacena en el nivel hoja del índice como una columna incluida.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 ;  

Columnas de claveKey Columns

Se recomienda insertar un número pequeño de columnas incluidas o de clave en la definición de un índice filtrado e incorporar solamente las columnas necesarias para que el optimizador de consultas elija el índice filtrado para el plan de ejecución de consultas.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. El optimizador de consultas puede elegir un índice filtrado para la consulta, independientemente de que cubra la consulta o no.The query optimizer can choose a filtered index for the query regardless of whether it does or does not cover the query. Sin embargo, es más probable que el optimizador de consultas elija un índice filtrado si cubre la consulta.However, the query optimizer is more likely to choose a filtered index if it covers the query.

En algunos casos, un índice filtrado cubre la consulta sin incluir las columnas en la expresión del índice filtrado como columnas incluidas o de clave en la definición del í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. Las instrucciones siguientes explican los casos en que una columna de la expresión del índice filtrado debe ser una columna incluida o de clave en la definición del í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. Los ejemplos hacen referencia al índice filtrado FIBillOfMaterialsWithEndDate que se creó previamente.The examples refer to the filtered index, FIBillOfMaterialsWithEndDate that was created previously.

Una columna de la expresión del índice filtrado no tiene por qué ser una columna incluida o de clave en la definición del índice filtrado cuando la expresión del índice filtrado es equivalente al predicado de la consulta y la consulta no devuelve la columna de la expresión del índice filtrado con los resultados de la 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 ejemplo, FIBillOfMaterialsWithEndDate cubre la consulta siguiente porque el predicado de consulta es equivalente a la expresión del filtro, y EndDate no se devuelve con los resultados de la 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 no necesita EndDate como una columna incluida o de clave en la definición del í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;   

Una columna de la expresión del índice filtrado debe ser una columna incluida o de clave de la definición del índice filtrado cuando el predicado de la consulta usa la columna en una comparación no equivalente a la expresión del í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 ejemplo, FIBillOfMaterialsWithEndDate es válido para la consulta siguiente porque selecciona un subconjunto de filas del índice filtrado.For example, FIBillOfMaterialsWithEndDate is valid for the following query because it selects a subset of rows from the filtered index. Sin embargo, no cubre la consulta siguiente porque EndDate se utiliza en la comparación EndDate > '20040101', que no es equivalente a la expresión del í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. El procesador de consultas no puede ejecutar esta consulta si no busca los valores de EndDate.The query processor cannot execute this query without looking up the values of EndDate. Por tanto, EndDate debe ser una columna incluida o de clave de la definición del í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';   

Una columna de la expresión del índice filtrado debe ser una columna incluida o de clave en la definición del índice filtrado si la columna está en el conjunto de resultados de la 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 ejemplo, FIBillOfMaterialsWithEndDate no atiende la consulta siguiente porque devuelve la columna EndDate en los resultados de la consulta.For example, FIBillOfMaterialsWithEndDate does not cover the following query because it returns the EndDate column in the query results. Por tanto, EndDate debe ser una columna incluida o de clave de la definición del í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;  

La clave de índice clúster de la tabla no tiene por qué ser una columna incluida o de clave de la definición del índice filtrado.The clustered index key of the table does not need to be a key or included column in the filtered index definition. La clave de índice cluster se incluye de forma automática en todos los índices no clúster, incluidos los índices filtrados.The clustered index key is automatically included in all nonclustered indexes, including filtered indexes.

Operadores de conversión de datos en el predicado de filtroData Conversion Operators in the Filter Predicate

Si el operador de comparación especificado en la expresión del índice filtrado del índice filtrado produce una conversión de datos implícita o explícita, se producirá un error cuando la conversión se realice en el lado izquierdo de un operador de comparación.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. Una posible solución es escribir la expresión del índice filtrado con el operador de conversión de datos (CAST o CONVERT) en el lado derecho del operador de comparación.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.

En el ejemplo siguiente se crea una tabla con varios tipos de datos.The following example creates a table with a variety of data types.

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

En la siguiente definición del índice filtrado, la columna b se convierte implícitamente en un tipo de datos enteros para que se pueda comparar con la 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. Esto genera el mensaje de error 10611 porque la conversión se produce en el lado izquierdo del operador del 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;  

La solución consiste en convertir la constante del lado derecho de forma que sea del mismo tipo que la columna b, tal como se muestra en el ejemplo siguiente: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);  

Cuando se mueve la conversión de datos del lado izquierdo al lado derecho de un operador de comparación, es posible que cambie el significado de la conversión.Moving the data conversion from the left side to the right side of a comparison operator might change the meaning of the conversion. En el ejemplo anterior, cuando se agregó el operador CONVERT en el lado derecho, la comparación cambió de una comparación de enteros a una comparación 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.

Directrices para diseñar índices de almacén de columnasColumnstore Index Design Guidelines

Un columnstore index es una tecnología de almacenamiento, recuperación y administración de datos que emplea un formato de datos en columnas denominado almacén de columnas.A columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore. Para obtener más información, consulte Introducción a los índices de almacén de columnas.For more information, refer to Columnstore Indexes overview.

Para obtener información sobre la versión, consulte Novedades de los índices de almacén de columnas.For version information, see Columnstore indexes - What's new.

Arquitectura de los índices de almacén de columnasColumnstore Index Architecture

Si conoce estos conceptos básicos, le resultará más fácil entender otros artículos sobre almacenes de columnas que explican cómo utilizarlos de forma eficaz.Knowing these basics will make it easier to understand other columnstore articles that explain how to use them effectively.

El almacén de datos emplea la compresión de almacén de columnas y de filasData storage uses columnstore and rowstore compression

Al tratar los índices de almacén de columnas, se usan los términos almacén de filas y almacén de columnas para hacer hincapié en el formato del almacenamiento de datos.When discussing columnstore indexes, we use the terms rowstore and columnstore to emphasize the format for the data storage. Los índices de almacén de columnas utilizan estos dos tipos de almacenamiento.Columnstore indexes use both types of storage.

Clustered Columnstore IndexClustered Columnstore Index

  • Un almacén de columnas son datos organizados lógicamente como una tabla con filas y columnas, y almacenados físicamente en un formato de columnas.A columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format.

    Un índice de almacén almacena físicamente la mayoría de los datos en formato de almacén de columnas.A columnstore index physically stores most of the data in columnstore format. Con este formato, los datos se comprimen y descomprimen como columnas.In columnstore format, the data is compressed and uncompressed as columns. No hace falta descomprimir otros valores que no haya solicitado la consulta en cada una de las filas.There is no need to uncompress other values in each row that are not requested by the query. De este modo, se puede examinar rápida una columna entera de una tabla grande.This makes it fast to scan an entire column of a large table.

  • Un almacén de filas son datos organizados lógicamente como una tabla con filas y columnas, y almacenados físicamente después en un formato de filas.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. Esta ha sido la forma tradicional de almacenar datos de tablas relacionales, como un índice de montón o de árbol B agrupado.This has been the traditional way to store relational table data such as a heap or clustered B-tree index.

    Un índice de almacén de columnas también guarda físicamente algunas filas en un formato de almacén de filas denominado "almacén delta" (también llamado "grupos de filas delta").A columnstore index also physically stores some rows in a rowstore format called a deltastore. Se trata de un lugar donde se colocan las filas que son insuficientes para poder comprimirse en el almacén de columnas.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 grupo de filas delta se implementa como un índice de árbol B agrupado.Each delta rowgroup is implemented as a clustered B-tree index.

  • El almacén delta es un lugar donde se colocan las filas que son insuficientes para poder comprimirse en el almacén de columnas.The deltastore is a holding place for rows that are too few in number to be compressed into the columnstore. En el almacén delta se almacenan las filas en formato de almacén de filas.The deltastore stores the rows in rowstore format.

Las operaciones se realizan en segmentos de columna y grupos de filasOperations are performed on rowgroups and column segments

El índice de almacén de columnas agrupa las filas en unidades administrables.The columnstore index groups rows into manageable units. Cada una de estas unidades se denomina grupo de filas.Each of these units is called a rowgroup. Para obtener el mejor rendimiento, el número de filas del grupo de filas debe ser suficientemente grande como para mejorar las tasas de compresión, y suficientemente pequeño como para beneficiarse de las operaciones en memoria.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 ejemplo, el índice de almacén de columnas realiza estas operaciones en grupos de filas:For example, the columnstore index performs these operations on rowgroups:

  • Comprime los grupos de filas en el almacén de columnas.Compresses rowgroups into the columnstore. La compresión se realiza en cada segmento de columna de un grupo de filas.Compression is performed on each column segment within a rowgroup.
  • Combina los grupos de filas durante una operación ALTER INDEX ... REORGANIZE.Merges rowgroups during an ALTER INDEX ... REORGANIZE operation.
  • Crea grupos de filas durante una operación ALTER INDEX ... REBUILD.Creates new rowgroups during an ALTER INDEX ... REBUILD operation.
  • Informa de la fragmentación y el estado de los grupos de filas en las vistas de administración dinámica (DMV).Reports on rowgroup health and fragmentation in the dynamic management views (DMVs).

El almacén delta se compone de uno o varios grupos de filas denominados grupos de filas delta.The deltastore is comprised of one or more rowgroups called delta rowgroups. Cada grupo de filas delta es un índice de árbol B agrupado que almacena operaciones de carga e inserción masivas pequeñas hasta que el grupo de filas contiene 1 048 576 filas, o bien hasta que se vuelve a generar el índice.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. Cuando un grupo de filas delta tiene 1 048 576 filas, se marca como cerrado y espera a que un proceso llamado "motor de tupla" lo comprima en el almacén de columnas.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 columna tiene algunos de sus valores en cada grupo de filas.Each column has some of its values in each rowgroup. Estos valores se denominan segmentos de columna.These values are called column segments. Cada grupo de filas contiene un segmento de cada columna de la tabla.Each rowgroup contains one column segment for every column in the table. Cada columna tiene un segmento de columna en cada grupo de filas.Each column has one column segment in each rowgroup.

Column segmentColumn segment

Cuando el índice de almacén de columnas comprime un grupo de filas, lo hace con cada segmento de columna de manera independiente.When the columnstore index compresses a rowgroup, it compresses each column segment separately. Para descomprimir una columna entera, el índice de almacén de columnas solo debe descomprimir un segmento de columna de cada grupo de filas.To uncompress an entire column, the columnstore index only needs to uncompress one column segment from each rowgroup.

Las inserciones y las cargas pequeñas pasan al almacén deltaSmall loads and inserts go to the deltastore

Un índice de almacén de columnas mejora el rendimiento y la compresión del almacén de columnas comprimiendo, al menos, 102 400 filas a la vez en el índice de almacén de columnas.A columnstore index improves columnstore compression and performance by compressing at least 102,400 rows at a time into the columnstore index. Para comprimir las filas de forma masiva, el índice de almacén de columnas acumula inserciones y cargas pequeñas en el almacén delta.To compress rows in bulk, the columnstore index accumulates small loads and inserts in the deltastore. Las operaciones del almacén delta se administran en segundo plano.The deltastore operations are handled behind the scenes. Para devolver los resultados correctos de la consulta, el índice clúster de almacén de columnas combina los resultados de la consulta tanto del almacén de columnas como del almacén delta.To return the correct query results, the clustered columnstore index combines query results from both the columnstore and the deltastore.

Las filas pasan al almacén delta cuando se dan estas circunstancias:Rows go to the deltastore when they are:

  • Insertado con la instrucción INSERT INTO ... VALUES.Inserted with the INSERT INTO ... VALUES statement.
  • Al final de una carga masiva y cuando tienen menos de 102 400 filas.At the end of a bulk load and they number less than 102,400.
  • Actualizado.Updated. Cada actualización se implementa como una eliminación y una inserción.Each update is implemented as a delete and an insert.

El almacén delta también guarda una lista de identificadores de las filas eliminadas que se han marcado como eliminadas, pero que aún no se han suprimido físicamente del almacén de columnas.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.

Cuando se llenan los grupos de filas delta se comprimen en el almacén de columnasWhen delta rowgroups are full they get compressed into the columnstore

Los índices agrupados de almacén de columnas recopilan hasta 1 048 576 filas en cada grupo de filas delta antes de comprimir dicho grupo de filas en el almacén de columnas.Clustered columnstore indexes collect up to 1,048,576 rows in each delta rowgroup before compressing the rowgroup into the columnstore. De este modo, se mejora la compresión del índice de almacén de columnas.This improves the compression of the columnstore index. Cuando un grupo de filas de almacén delta contiene 1 048 576 filas, el índice de almacén de columnas lo marca como cerrado.When a delta rowgroup contains 1,048,576 rows, the columnstore index marks the rowgroup as closed. Un proceso en segundo plano denominado" motor de tupla" identifica cada grupo de filas marcado como cerrado y lo comprime en el almacén de columnas.A background process, called the tuple-mover, finds each closed rowgroup and compresses it into the columnstore.

Puede forzar grupos de filas delta del almacén de columnas mediante ALTER INDEX para generar o reorganizar el índice.You can force delta rowgroups into the columnstore by using ALTER INDEX to rebuild or reorganize the index. Tenga en cuenta que si hay presión de memoria durante la compresión, el índice de almacén de columnas podría reducir el número de filas de filas en el grupo de filas comprimido.Note that if there is memory pressure during compression, the columnstore index might reduce the number of rows in the compressed rowgroup.

Cada partición de tabla tiene sus propios grupos de filas y grupos de filas deltaEach table partition has its own rowgroups and delta rowgroups

El concepto de partición es el mismo en un índice agrupado, un montón y un índice de almacén de columnas.The concept of partitioning is the same in both a clustered index, a heap, and a columnstore index. Al crear particiones de una tabla, esta se divide en grupos de filas más pequeños según un rango de valores de columna.Partitioning a table divides the table into smaller groups of rows according to a range of column values. A menudo, se utiliza para administrar los datos.It is often used for managing the data. Por ejemplo, podría crear una partición de cada año de datos y, más adelante, usar la opción de modificación de particiones para archivar los datos en un almacenamiento menos costoso.For example, you could create a partition for each year of data, and then use partition switching to archive data to less expensive storage. Esta característica funciona en los índices de almacén de columnas y facilita la tarea de mover una partición de datos a otra ubicación.Partition switching works on columnstore indexes and makes it easy to move a partition of data to another location.

Los grupos de filas siempre se definen dentro de una partición de tabla.Rowgroups are always defined within a table partition. Cuando un índice de almacén de columnas tiene particiones, cada una de ellas tiene sus propios grupos de filas y grupos de filas delta comprimidos.When a columnstore index is partitioned, each partition has its own compressed rowgroups and delta rowgroups.

Cada partición puede contener varios grupos de filas deltaEach partition can have multiple delta rowgroups

Cada partición puede tener más de un grupos de filas delta.Each partition can have more than one delta rowgroups. Cuando el índice de almacén de columnas necesita agregar datos a un grupo de filas delta y este se ha bloqueado, el índice de almacén de columnas tratará de obtener un bloqueo en otro grupo de filas delta.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. Si no hay ningún grupo de filas delta disponible, el índice de almacén columnas creará un grupo de filas delta.If there are no delta rowgroups available, the columnstore index will create a new delta rowgroup. Por ejemplo, una tabla con 10 particiones podría tener fácilmente 20 o más grupos de filas delta.For example, a table with 10 partitions could easily have 20 or more delta rowgroups.

Puede combinar los índices de almacén de filas y de columnas en la misma tablaYou can combine columnstore and rowstore indexes on the same table

Un índice no agrupado contiene una copia de parte o la totalidad de las filas y columnas de la tabla subyacente.A nonclustered index contains a copy of part or all of the rows and columns in the underlying table. El índice se define como una o varias columnas de la tabla y tiene una condición opcional que filtra las filas.The index is defined as one or more columns of the table, and has an optional condition that filters the rows.

A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x), puede crear un índice de almacén de columnas no agrupado actualizable en una tabla de almacén de filas.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create an updatable nonclustered columnstore index on a rowstore table. El índice de columnas almacena una copia de los datos, por lo que necesita más almacenamiento.The columnstore index stores a copy of the data so you do need extra storage. Sin embargo, los datos del índice de almacén de columnas se comprimen en un tamaño inferior al que requiere la tabla de almacén de filas.However, the data in the columnstore index will compress to a smaller size than the rowstore table requires. Gracias a esto, se pueden ejecutar análisis en el índice de almacén de columnas y realizar transacciones en el índice de almacén de filas al mismo tiempo.By doing this, you can run analytics on the columnstore index and transactions on the rowstore index at the same time. El almacén de columnas se actualiza cuando cambian los datos de la tabla de almacén de filas, de modo que ambos índices trabajan con los mismos datos.The column store is updated when data changes in the rowstore table, so both indexes are working against the same data.

A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x), puede tener uno o varios índices de almacén de filas no agrupados en un índice de almacén de columnas.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. Gracias a ello, podrá realizar búsquedas de tabla eficaces en el almacén de columnas subyacente.By doing this, you can perform efficient table seeks on the underlying columnstore. También habrá disponibles otras opciones.Other options become available too. Por ejemplo, podrá aplicar una restricción de clave principal mediante una restricción UNIQUE en la tabla de almacén de filas.For example, you can enforce a primary key constraint by using a UNIQUE constraint on the rowstore table. Puesto que un valor que no es único no se insertará en la tabla de almacén de filas, SQL Server no podrá insertar ese valor en el almacén de columnas.Since an non-unique value will fail to insert into the rowstore table, SQL Server cannot insert the value into the columnstore.

Consideraciones de rendimientoPerformance considerations

  • La definición del índice de almacén de columnas no agrupado admite el uso de una condición de filtrado.The nonclustered columnstore index definition supports using a filtered condition. Para minimizar el impacto de rendimiento que tiene agregar un índice de almacén de columnas a una tabla OLTP, use una condición de filtrado para crear un índice de almacén de columnas no agrupado únicamente en los datos inactivos de la carga de trabajo operativa.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.

  • Las tablas en memoria pueden tener un índice de almacén de columnas.An in-memory table can have one columnstore index. Puede crearlo cuando se genere la tabla o agregarlo en otro momento con ALTER TABLE (Transact-SQL).You can create it when the table is created or add it later with ALTER TABLE (Transact-SQL). Antes de SQL Server 2016 (13.x)SQL Server 2016 (13.x), solo las tablas basadas en disco podían contar con un índice de almacén de columnas.Before SQL Server 2016 (13.x)SQL Server 2016 (13.x), only a disk-based table could have a columnstore index.

Para obtener más información, consulte Rendimiento de las consultas de índices de almacén de columnas.For more information, refer to Columnstore indexes - Query performance.

Guía de diseñoDesign Guidance

  • Una tabla de almacén de filas puede contar con un índice de almacén de columnas no agrupado actualizable.A rowstore table can have one updateable nonclustered columnstore index. Antes de SQL Server 2014 (12.x)SQL Server 2014 (12.x), el índice de almacén de columnas no agrupado era de solo lectura.Before SQL Server 2014 (12.x)SQL Server 2014 (12.x), the nonclustered columnstore index was read-only.

Para obtener más información, consulte Guía de diseño de índices de almacén de columnas.For more information, refer to Columnstore indexes - Design Guidance.

Directrices para diseñar índices de hashHash Index Design Guidelines

Todas las tablas optimizadas para memoria deben tener como mínimo un índice porque son los índices los que conectan las filas.All memory-optimized tables must have at least one index, because it is the indexes that connect the rows together. En una tabla optimizada para memoria, todos los índices también son optimizados para memoria.On a memory-optimized table, every index is also memory-optimized. Los índices de hash son uno de los tipos de índice posibles en una tabla optimizada para memoria.Hash indexes are one of the possible index types in a memory-optimized table. Para obtener más información, vea Índices de tablas optimizadas para memoria.For more information, see Indexes for Memory-Optimized Tables.

Se aplica a: desde SQL Server 2014 (12.x)SQL Server 2014 (12.x) hasta SQL Server 2017SQL Server 2017.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017.

Arquitectura de los índices de hashHash Index Architecture

Los índices de hash constan de una matriz de punteros, y cada elemento de la matriz se llama "cubo de hash".A hash index consists of an array of pointers, and each element of the array is called a hash bucket.

  • Cada depósito tiene 8 bytes, que se usan para almacenar la dirección de memoria de una lista de vínculos de entradas de índice.Each bucket is 8 bytes, which are used to store the memory address of a link list of key entries.
  • Cada entrada es un valor correspondiente a una clave de índice, además de la dirección de su fila correspondiente en la tabla subyacente optimizada para memoria.Each entry is a value for an index key, plus the address of its corresponding row in the underlying memory-optimized table.
  • Cada entrada apunta a la siguiente entrada en una lista de vínculos de entradas, todas ellas encadenadas al depósito actual.Each entry points to the next entry in a link list of entries, all chained to the current bucket.

El número de cubos debe especificarse en el momento de definir los índices:The number of buckets must be specified at index definition time:

  • Cuanto menor sea la proporción de depósitos con respecto a las filas de la tabla o valores distintos, mas larga será la lista de vínculos de depósito promedio.The lower the ratio of buckets to table rows or to distinct values, the longer the average bucket link list will be.
  • Las listas de vínculos cortas se ejecutan más rápidamente que las listas de vínculos largas.Short link lists perform faster than long link lists.
  • El número máximo de cubos en los índices de hash es de 1 073 741 824.The maximum number of buckets in hash indexes is 1,073,741,824.

Sugerencia

Para determinar el BUCKET_COUNT correcto para los datos, consulte Configuración del número de cubos de índice de hash.To determine the right BUCKET_COUNT for your data, see Configuring the hash index bucket count.

La función hash se aplica a las columnas de clave de índice, y el resultado de la función determina a qué contenedor pertenece dicha clave.The hash function is applied to the index key columns and the result of the function determines what bucket that key falls into. Los cubos tienen un puntero a las filas cuyos valores de clave con hash se asignan a estos cubos.Each bucket has a pointer to rows whose hashed key values are mapped to that bucket.

La función hash que se utiliza para los índices hash tiene las siguientes características:The hashing function used for hash indexes has the following characteristics:

  • SQL ServerSQL Server tiene una función hash que se utiliza para todos los índices hash.has one hash function that is used for all hash indexes.
  • La función hash es determinista.The hash function is deterministic. Siempre se asigna el mismo valor de clave de entrada al mismo cubo del índice de hash.The same input key value is always mapped to the same bucket in the hash index.
  • Se pueden asignar múltiples claves de índice al mismo depósito de hash.Multiple index keys may be mapped to the same hash bucket.
  • La función hash está equilibrada, lo que significa que los valores de clave de índice en los cubos de hash siguen normalmente una distribución normal o de Poisson, no una distribución plana lineal.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.
  • La distribución de Poisson no es una distribución uniforme.Poisson distribution is not an even distribution. Los valores de clave de índice no se distribuyen uniformemente en cubos de hash.Index key values are not evenly distributed in the hash buckets.
  • Si dos claves de índice se asignan al mismo cubo de hash, hay una colisión de hash.If two index keys are mapped to the same hash bucket, there is a hash collision. Un gran número de colisiones de valores hash puede tener un impacto en el rendimiento de las operaciones de lectura.A large number of hash collisions can have a performance impact on read operations. Un objetivo realista es que el 30 % de los cubos contengan dos valores de clave diferentes.A realistic goal is for 30% of the buckets contain two different key values.

La interacción del índice de hash y los cubos se resume en la siguiente imagen.The interplay of the hash index and the buckets is summarized in the following image.

hekaton_tables_23dhekaton_tables_23d

Configuración del número de cubos de índice de hashConfiguring the hash index bucket count

El número de cubos de índice de hash se especifica al crear el índice y se puede modificar con la sintaxis 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.

En la mayoría de los casos, lo ideal es que el número de cubos esté entre 1 y 2 veces el número de valores distintos de la clave 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.
Es posible que no siempre consiga predecir cuántos valores puede tener o tendrá una clave de índice determinada.You may not always be able to predict how many values a particular index key may have, or will have. El rendimiento sigue siendo bueno por lo general si el valor BUCKET_COUNT está dentro de 10 veces el número real de valores de clave. A este respecto, suele ser mejor realizar estimaciones por lo alto que por lo bajo.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.

Un número muy pequeño de cubos tiene las siguientes desventajas:Too few buckets has the following drawbacks:

  • Más colisiones de hash de valores de clave distintos.More hash collisions of distinct key values.
  • Cada valor distinto está obligado a compartir el mismo cubo con otro valor distinto.Each distinct value is forced to share the same bucket with a different distinct value.
  • Aumenta el promedio de longitud de cadena por cubo.The average chain length per bucket grows.
  • Cuanto más larga sea la cadena de cubos, menor será la velocidad de búsquedas de igualdad en el índice.The longer the bucket chain, the slower the speed of equality lookups in the index.

Un número muy alto de cubos tiene las siguientes desventajas::Too many buckets has the following drawbacks:

  • Un número excesivo de cubos puede generar más cubos vacíos.Too high a bucket count might result in more empty buckets.
  • Los depósitos vacíos repercuten en el rendimiento de los exámenes de índice completos.Empty buckets impact the performance of full index scans. Si se realizan con frecuencia, inclínese por un número de cubos cercano al número de valores de clave de índice distintos.If those are performed regularly, consider picking a bucket count close to the number of distinct index key values.
  • Los depósitos vacíos usan la memoria, aunque cada depósito emplea únicamente 8 bytes.Empty buckets use memory, though each bucket uses only 8 bytes.

Nota

Agregar más depósitos no hace nada para reducir el encadenamiento de entradas que comparten un valor duplicado.Adding more buckets does nothing to reduce the chaining together of entries that share a duplicate value. La tasa de duplicación de valores sirve para decidir si un tipo de índice de hash es el adecuado, no para calcular el número de cubos.The rate of value duplication is used to decide whether a hash is the appropriate index type, not to calculate the bucket count.

Consideraciones de rendimientoPerformance considerations

El rendimiento de un índice de hash es:The performance of a hash index is:

  • Excelente cuando el predicado de la cláusula WHERE contiene un valor exacto para cada columna de la clave de índice de hash.Excellent when the predicate in the WHERE clause specifies an exact value for each column in the hash index key. Un índice hash revertirá a un recorrido dado un predicado de desigualdad.A hash index will revert to a scan given an inequality predicate.
  • Deficiente cuando el predicado de la cláusula WHERE busca un rango de valores en la clave de índice.Poor when the predicate in the WHERE clause looks for a range of values in the index key.
  • Deficiente cuando el predicado de la cláusula WHERE establece un valor específico para la primera columna de una clave de índice de hash de dos columnas, pero ninguno para las otras columnas de la clave.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.

Sugerencia

El predicado debe incluir todas las columnas de clave de índice hash.The predicate must include all columns in the hash index key. El índice hash requiere una clave (de hash) para buscar en el índice.The hash index requires a key (to hash) to seek into the index. Si la clave de índice consta de dos columnas y la cláusula WHERE solo proporciona la primera, SQL ServerSQL Server no tiene una clave completa para aplicar el algoritmo 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. Esto generará un plan de consulta de examen de índice.This will result in an index scan query plan.

Si se usa un índice de hash y el número de claves de índice únicas es 100 veces (o más) el recuento de filas, es recomendable aumentar el número de cubos para evitar cadenas de filas grandes o usar un índice no agrupado.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.

Consideraciones de declaraciónDeclaration considerations

Solo puede existir un índice de hash en una tabla optimizada para memoria.A hash index can exist only on a memory-optimized table. No puede existir en una tabla basada en disco.It cannot exist on a disk-based table.

Un índice de hash se puede declarar como:A hash index can be declared as:

  • UNIQUE, o puede establecerse de forma predeterminada en Non-Unique.UNIQUE, or can default to Non-Unique.
  • NONCLUSTERED, que es el valor predeterminado.NONCLUSTERED, which is the default.

A continuación se muestra una sintaxis de ejemplo para crear un índice de hash fuera de la instrucción 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);

Versiones de fila y recolección de elementos no utilizadosRow versions and garbage collection

En una tabla optimizada para memoria, cuando una fila se ve afectada por una instrucción UPDATE, la tabla crea una versión actualizada de la fila.In a memory-optimized table, when a row is affected by an UPDATE, the table creates an updated version of the row. Durante la transacción de actualización, es posible que otras sesiones puedan leer la versión anterior de la fila y, por tanto, evitar la degradación del rendimiento asociada a un bloqueo de fila.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.

Puede que el índice de hash también tenga versiones diferentes de las entradas para dar cabida a la actualización.The hash index might also have different versions of its entries to accommodate the update.

Más adelante cuando las versiones anteriores ya no se necesiten, un subproceso de recolección de elementos no utilizados (GC) recorre transversalmente los cubos y sus listas de vínculos para eliminar las entradas más antiguas.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. El subproceso GC funciona mejor si las longitudes de cadena de las listas de vínculo son cortas.The GC thread performs better if the link list chain lengths are short. Para obtener más información, consulte Recolección de elementos no utilizados de OLTP en memoria.For more information, refer to In-Memory OLTP Garbage Collection.

Directrices para diseñar índices no agrupados optimizados para memoriaMemory-Optimized Nonclustered Index Design Guidelines

Los índices no agrupados son uno de los posibles tipos de índice de una tabla optimizada para memoria.Nonclustered indexes are one of the possible index types in a memory-optimized table. Para obtener más información, vea Índices de tablas optimizadas para memoria.For more information, see Indexes for Memory-Optimized Tables.

Se aplica a: desde SQL Server 2014 (12.x)SQL Server 2014 (12.x) hasta SQL Server 2017SQL Server 2017.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017.

Arquitectura de los índices no agrupados en memoriaIn-memory Nonclustered Index Architecture

Los índices no agrupados en memoria se implementan mediante una estructura de datos llamada "árbol BW", concebida y descrita originalmente por Microsoft Research en 2011.In-memory nonclustered indexes are implemented using a data structure called a Bw-Tree, originally envisioned and described by Microsoft Research in 2011. Un árbol BW es la variación sin bloqueos ni bloqueos temporales de un árbol B.A Bw-Tree is a lock and latch-free variation of a B-Tree. Para más información, consulte Bw-Tree: A B-tree for New Hardware Platforms (Árbol B: un árbol B para nuevas plataformas de hardware).For more details please see The Bw-Tree: A B-tree for New Hardware Platforms.

En un nivel muy alto, el árbol BW puede entenderse como una asignación de páginas organizadas por un identificador de página (PidMap), un recurso para asignar y reutilizar identificadores de página (PidAlloc), y un conjunto de páginas vinculadas a la asignación de páginas y también entre sí.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. Estos tres subcomponentes de alto nivel constituyen la estructura interna básica de un árbol BW.These three high level sub-components make up the basic internal structure of a Bw-Tree.

Su estructura es similar a la de un árbol B porque cada página tiene un conjunto de valores de clave que están ordenados. Además, en el índice hay niveles, y cada uno de ellos apunta a un nivel inferior. A su vez, los niveles hoja apuntan a una fila de datos.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. Sin embargo, hay varias diferencias.However there are several differences.

Al igual que los índices de hash, se pueden vincular varias filas de datos entre sí (versiones).Just like hash indexes, multiple data rows can be linked together (versions). Los punteros de página entre niveles son identificadores de página lógicos, es decir, desplazamientos a una tabla de asignación de páginas que, a su vez, contiene la dirección física 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.

No hay actualizaciones de páginas de índice en la ubicación.There are no in-place updates of index pages. Se han incorporado nuevas páginas delta con esta finalidad.New delta pages are introduced for this purpose.

  • Para las actualizaciones de página no es necesario ningún bloqueo o bloqueo temporal.No latching or locking is required for page updates.
  • Las páginas de índice no son de un tamaño fijo.Index pages are not a fixed size.

El valor de clave de cada página de nivel no hoja descrito es el valor más alto que contiene el elemento secundario al que apunta, y cada fila también contiene el identificador de página lógico.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. En las páginas de nivel hoja, junto con el valor de clave, contiene la dirección física de la fila de datos.On the leaf-level pages, along with the key value, it contains the physical address of the data row.

Las búsquedas de punto se parecen a los árboles B, pero son distintas en el sentido de que las páginas se vinculan solo en una dirección, Motor de base de datos de SQL ServerSQL Server Database Engine sigue los punteros de página derecha, en los que cada página que no es una hoja tiene el valor más alto de su elemento secundario, y no el más bajo, como en el caso de los árboles B.Point lookups are similar to B-Trees except that because pages are linked in only one direction, the Motor de base de datos de 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.

Si una página de nivel hoja debe cambiarse, el Motor de base de datos de SQL ServerSQL Server Database Engine no modifica la propia página.If a Leaf-level page has to change, the Motor de base de datos de SQL ServerSQL Server Database Engine does not modify the page itself. En su lugar, el Motor de base de datos de SQL ServerSQL Server Database Engine crea un registro delta que describe el cambio y lo anexa a la página anterior.Rather, the Motor de base de datos de SQL ServerSQL Server Database Engine creates a delta record that describes the change, and appends it to the previous page. Después, también actualiza la dirección de la tabla de asignación de página para dicha página anterior a la dirección del registro delta que se convierte en la dirección física de esta 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.

Hay tres operaciones distintas que pueden ser necesarias para administrar la estructura de un árbol BW: consolidación, división y combinación.There are three different operations that can be required for managing the structure of a Bw-Tree: consolidation, split and merge.

Consolidación de deltaDelta Consolidation

Una cadena larga de registros delta podría reducir el rendimiento de búsqueda porque podría significar que se están recorriendo cadenas largas al hacer una búsqueda mediante un í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. Si se agrega un nuevo registro delta a una cadena que ya contiene 16 elementos, los cambios en los registros delta se consolidarán en la página de índice a la que se hace referencia. Después, la página se recompilará incluyendo los cambios indicados por el nuevo registro delta que haya desencadenado la consolidación.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. La página recompilada tendrá el mismo identificador de página pero una nueva dirección de memoria.The newly rebuilt page will have the same page ID but a new memory address.

hekaton_tables_23ehekaton_tables_23e

División de páginasSplit page

Los páginas de índice de un árbol BW se amplían en función de las necesidades: pueden almacenar una sola fila o bien un 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. Una vez que la página de índice llegue a los 8 KB, una nueva inserción de una sola fila hará que la página de índice se divida.Once the index page grows to 8 KB, a new insert of a single row will cause the index page to split. En el caso de las páginas internas, significa que no queda más espacio para agregar otro valor de clave y otro puntero. En el caso de las páginas hoja, significa que la fila sería demasiado grande para ajustarla a la página una vez que se incorporen todos los registros delta.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. La información de estadísticas que hay en el encabezado de una página hoja lleva a cabo un seguimiento de la cantidad de espacio que sería necesaria para consolidar los registros delta, y dicha información se ajusta a medida que se agrega un nuevo 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.

Se aplica una operación de división en dos pasos atómicos.A Split operation is done in two atomic steps. En la imagen que hay a continuación, pongamos que una página hoja fuerza una división porque se está insertando una clave con valor 5, y que hay una página no hoja que apunta al final de la página de nivel hoja (valor de clave 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

Paso 1: Asigne dos nuevas páginas P1 y P2, y divida las filas de la página P1 anterior en estas nuevas páginas, incluida la fila que se acaba de insertar.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. Se usa un nuevo espacio en la tabla de asignación de páginas para almacenar la dirección física de la página P2.A new slot in Page Mapping Table is used to store the physical address of page P2. Ninguna operación simultánea puede acceder todavía a estas páginas, P1 y P2.These pages, P1 and P2 are not accessible to any concurrent operations yet. Además, se establece el puntero lógico de P1 a P2.In addition, the logical pointer from P1 to P2 is set. Después, en un paso atómico, se actualiza la tabla de asignación de páginas para cambiar el puntero de la anterior P1 a la nueva P1.Then, in one atomic step update the Page Mapping Table to change the pointer from old P1 to new P1.

Paso 2: La página que no es hoja apunta a la página P1, pero no hay ningún puntero directo desde una página que no es hoja a la página P2.Step 2: The non-leaf page points to P1 but there is no direct pointer from a non-leaf page to P2. Solo se puede acceder a P2 mediante P1.P2 is only reachable via P1. Para crear un puntero de una página no hoja a una P2, asigne una nueva página no hoja (página de índice interno), copie todas las filas de la antigua página no hoja y agregue una nueva fila para que apunte a 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. Una vez lo haya hecho, en un paso atómico, actualice la tabla de asignación de páginas para cambiar el puntero de la antigua página no hoja a la nueva página no hoja.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.

Combinación de páginasMerge page

Cuando una operación DELETE provoca que una página tenga menos de un 10 % del tamaño máximo de página (actualmente, de 8 KB) o bien contenga una sola fila, la página se combinará con una contigua.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.

Cuando se elimina una fila de una página, se agrega un registro delta para la eliminación.When a row is deleted from a page, a delta record for the delete is added. Además, se hace una comprobación para determinar si la página de índice (página no hoja) cumple las condiciones de combinación.Additionally, a check is made to determine if the index page (non-leaf page) qualifies for Merge. En esta operación se comprueba si el espacio restante después de eliminar la fila será inferior al 10 % del tamaño máximo de página.This check verifies if the remaining space after deleting the row will be less than 10% of maximum page size. Si cumple las condiciones, se lleva a cabo la combinación en tres pasos atómicos.If it does qualify, the Merge is performed in three atomic steps.

En la imagen siguiente, pongamos que una operación DELETE elimina el valor de clave 10.In the picture below, assume a DELETE operation will delete the key value 10.

hekaton_tables_23ghekaton_tables_23g

Paso 1: Se crea una página delta que representa el valor de clave 10 (triángulo azul), y el puntero correspondiente de la página que no es hoja Pp1 se establece en la nueva 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. Además, se crea una página delta de combinación (triángulo verde) y se vincula para que apunte a la página delta.Additionally a special merge-delta page (green triangle) is created, and it is linked to point to the delta page. En este punto, ninguna transacción simultánea puede ver las dos páginas (delta y delta de combinación).At this stage, both pages (delta page and merge-delta page) are not visible to any concurrent transaction. En un paso atómico, el puntero a la página de nivel hoja P1 de la tabla de asignación de páginas se actualiza para que apunte a la página delta de combinación.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. Después de este paso, la entrada del valor de clave 10 en Pp1 apuntará a la página delta de combinación.After this step, the entry for key value 10 in Pp1 now points to the merge-delta page.

Paso 2: Debe eliminarse la fila que representa el valor de clave 7 en la página que no es hoja Pp1 y debe actualizarse la entrada del valor de clave 10 para que apunte a 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 ello, se asigna una nueva página no hoja Pp2 y todas las filas de Pp1 se copian, excepto la que representa el valor de clave 7. Después, la fila del valor de clave 10 se actualiza para que apunte a la 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. Una vez hecho esto, en un paso atómico, la entrada de la tabla de asignación de páginas que apunta a Pp1 se actualiza para que apunte a Pp2.Once this is done, in one atomic step, the Page Mapping Table entry pointing to Pp1 is updated to point to Pp2. Ya no se puede acceder a Pp1.Pp1 is no longer reachable.

Paso 3: Se combinan las páginas de nivel de hoja P2 y P1, y se eliminan las páginas delta.Step 3: The Leaf-level pages P2 and P1 are merged and the delta pages removed. Para ello, se asigna una nueva página P3, se combinan las filas de la P2 a la P1 y los cambios de la página delta se incluyen en la nueva 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. Después, en un paso atómico, la entrada de la tabla de asignación de páginas que apunta a la página P1 se actualiza para que apunte a la página P3.Then, in one atomic step, the Page Mapping Table entry pointing to page P1 is updated to point to page P3.

Consideraciones de rendimientoPerformance considerations

Al consultar una tabla optimizada para memoria con predicados de desigualdad, el rendimiento de los índices no agrupados es superior al de los índices de hash no agrupados.The performance of a nonclustered index is better than nonclustered hash indexes when querying a memory-optimized table with inequality predicates.

Nota

Una columna de una tabla optimizada para memoria puede formar parte de un índice hash y de un índice no clúster.A column in a memory-optimized table can be part of both a hash index and a nonclustered index.

Sugerencia

Cuando una columna de una serie de columnas de clave de índice no agrupado tiene muchos valores duplicados, el rendimiento puede reducirse para las actualizaciones, inserciones y eliminaciones.When a column in a nonclustered index key columns have many duplicate values, performance can degrade for updates, inserts, and deletes. Una manera de mejorar el rendimiento en este escenario es agregar otra columna al índice no clúster.One way to improve performance in this situation is to add another column to the nonclustered index.

Lecturas adicionalesAdditional 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 y volver a generar índices Reorganize and Rebuild Indexes
Mejorar el rendimiento con vistas indizadas de SQL Server 2008Improving Performance with SQL Server 2008 Indexed Views
Partitioned Tables and IndexesPartitioned Tables and Indexes
Creación de una clave principal Create a Primary Key
Índices de tablas con optimización para memoriaIndexes for Memory-Optimized Tables
Introducción a los índices de almacén de columnasColumnstore Indexes overview
Solución de problemas de índices de hash de tablas optimizadas para memoria Troubleshooting Hash Indexes for Memory-Optimized Tables
Vistas de administración dinámica de tablas optimizadas para memoria (Transact-SQL) Memory-Optimized Table Dynamic Management Views (Transact-SQL)
Funciones y vistas de administración dinámica relacionadas con índices (Transact-SQL) Index Related Dynamic Management Views and Functions (Transact-SQL)
Índices en columnas calculadas Indexes on Computed Columns
Índices y ALTER TABLE Indexes and ALTER TABLE
Desfragmentación de índice adaptableAdaptive Index Defrag