Índice con columnas incluidas

  • Artículo

En SQL Server 2005, puede ampliar la funcionalidad de índices no agrupados agregando columnas sin clave en el nivel hoja de un índice no agrupado. Al incluir columnas sin clave, puede crear índices no agrupados que abarcan más consultas. Esto se debe a que las columnas sin clave tienen las siguientes ventajas:

  • Pueden ser tipos de datos que no están permitidos como columnas de clave de índice.
  • El Database Engine (Motor de base de datos) no las tiene en cuenta cuando calcula el número de columnas de clave de índice o el tamaño de las claves de índice.

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. 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 agrupado, lo que da como resultado menos operaciones de E/S de disco.

[!NOTA] Cuando un índice contiene todas las columnas a las que hace referencia la consulta, normalmente se dice que abarca la consulta.

Las columnas de clave se almacenan en todos los niveles del índice, mientras que las columnas sin clave sólo se almacenan en el nivel hoja. Para obtener información acerca de los niveles de índice, vea Organización de tablas e índices.

Utilizar columnas incluidas para evitar límites de tamaño

Puede incluir columnas sin clave en un índice no agrupado para evitar sobrepasar 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. El Database Engine (Motor de base de datos) no tiene en cuenta las columnas sin clave al calcular el número de columnas de clave de índice o el tamaño de las claves de índice.

Por ejemplo, suponga que desea indizar las siguientes columnas de la tabla Document de la base de datos de ejemplo AdventureWorks:

     Title nvarchar(50)

     Revision nchar(5)

     FileName nvarchar(400)

Debido a que los tipos de datos nchar y nvarchar requieren 2 bytes para cada carácter, un índice que contenga estas tres columnas sobrepasaría la limitación de tamaño de 900 bytes en 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. 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. La siguiente instrucción crea ese índice.

USE AdventureWorks;
GO
CREATE INDEX IX_Document_Title       
ON Production.Document (Title, Revision)       
INCLUDE (FileName);

Directrices del índice con columnas incluidas

Cuando diseñe índices no agrupados con columnas incluidas tenga en cuenta las siguientes directrices:

  • Las columnas sin clave se definen en la cláusula INCLUDE de la instrucción CREATE INDEX.
  • Las columnas sin clave sólo se pueden definir en índices no agrupados en tablas o vistas indizadas.
  • Se permiten todos los tipos de datos, excepto text, ntext e image.
  • Las columnas calculadas que son deterministas y precisas o imprecisas pueden ser columnas incluidas. Para obtener más información, vea Crear índices en columnas calculadas.
  • Al igual que con las columnas de clave, las columnas calculadas derivadas de los tipos de datos image, ntext y 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.
  • Los nombres de columna no se pueden especificar en la lista INCLUDE y en la lista de columnas de clave.
  • Los nombres de columna no se pueden repetir en la lista INCLUDE.

Directrices del tamaño de columnas

  • Es necesario definir como mínimo una columna de clave. El número máximo de columnas sin clave es de 1023 columnas. Éste es el número máximo de columnas de la tabla menos 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.
  • El tamaño total de todas las columnas sin clave sólo 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.

Directrices para modificar columnas

Cuando se modifica una columna de tabla que se ha definido como una columna incluida, se aplican las siguientes restricciones:

  • Las columnas sin clave no se pueden quitar de la tabla, a menos que antes se quite el índice.
  • Las columnas sin clave no se pueden cambiar, excepto para hacer lo siguiente:

    • Cambiar la capacidad de aceptar valores NULL de NOT NULL a NULL.

    • Aumentar la longitud de las columnas varchar, nvarchar o varbinary.

      [!NOTA] También se aplican restricciones de modificación a las columnas de clave de índice.

Recomendaciones de diseño

Rediseñe índices no agrupados con un tamaño de las claves de índice grande para que sólo las columnas utilizadas para búsquedas sean columnas de clave. Haga que todas las demás columnas que abarcan la consulta sean columnas sin clave incluidas. De esta forma, tendrá todas las columnas necesarias para abarcar la consulta pero la clave de índice en sí será pequeña y eficaz.

Por ejemplo, suponga que desea diseñar un índice para abarcar la siguiente consulta.

USE AdventureWorks;
GO
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. Aunque puede definir todas las columnas como columnas de clave, el tamaño de clave debe ser de 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.

La siguiente instrucción crea un índice con columnas incluidas para abarcar la consulta.

USE AdventureWorks;
GO
CREATE INDEX IX_Address_PostalCode       
ON Person.Address (PostalCode)       
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

Consideraciones de rendimiento

Evite agregar columnas que no sean necesarias. El hecho de agregar demasiadas columnas de índice, con o sin clave, puede tener las siguientes consecuencias en el rendimiento:

  • Cabrán menos filas de índice en una página. Esto puede crear incrementos de E/S y una reducción de la eficacia de la caché.
  • Se necesitará más espacio en disco para almacenar el índice. 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. Esto se debe a que los valores de columnas se copian en el nivel hoja del índice. Por lo tanto, residen en el índice y en la tabla base.
  • 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.

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. Para obtener más información acerca de la evaluación del rendimiento de las consultas, vea Optimizar consultas.

Vea también

Conceptos

Crear índices (motor de base de datos)
Crear índices con columnas incluidas
Directrices generales para diseñar índices
Conceptos básicos del diseño de índices
Tamaño máximo de las claves de índices
Ver información de índice

Ayuda e información

Obtener ayuda sobre SQL Server 2005