Artículo
03/30/2012

Crear índices (motor de base de datos)

En este tema se describen las principales tareas de creación de índices y se proporcionan las directrices de rendimiento e implementación que hay que tener en cuenta antes de crear un índice.

Tareas de creación de índices

Las siguientes tareas forman parte de la estrategia recomendada para crear índices:

Diseñar el índice.

El diseño de índices es una tarea crítica. El diseño de índices incluye la determinación de las columnas que se utilizarán, la selección del tipo de índice (por ejemplo, agrupado o no agrupado), la selección de opciones de índice adecuadas y la determinación de grupos de archivos o de la ubicación de esquemas de partición. Para obtener más información, vea Diseñar índices.
Determinar el mejor método de creación. Los índices se crean de las siguientes maneras:
- Definiendo una restricción PRIMARY KEY o UNIQUE en una columna mediante CREATE TABLE o ALTER TABLE
  
  SQL Server Database Engine (Motor de base de datos de SQL Server) crea automáticamente un índice único para hacer cumplir los requisitos de unicidad de una restricción PRIMARY KEY o UNIQUE. De forma predeterminada se crea un índice clúster único para hacer cumplir una restricción PRIMARY KEY, a menos que ya exista un índice clúster en la tabla o que usted especifique un índice no clúster único. De forma predeterminada se crea un índice único no clúster para hacer cumplir una restricción UNIQUE a menos que se especifique de explícitamente un índice clúster único y no exista un índice clúster en la tabla.
  
  También se pueden especificar las opciones de índice, la ubicación del índice, el grupo de archivos o el esquema de la partición.
  
  Un índice creado como parte de una restricción PRIMARY KEY o UNIQUE recibe automáticamente el mismo nombre que la restricción. Para obtener más información, vea Restricciones PRIMARY KEY y Restricciones UNIQUE.
- Creando un índice independiente de una restricción utilizando la instrucción CREATE INDEX , o el cuadro de diálogo Nuevo índice en el Explorador de objetos de SQL Server Management Studio
  
  Debe especificar el nombre del índice, de la tabla y de las columnas a las que se aplica el índice. También se pueden especificar las opciones de índice, la ubicación del índice, el grupo de archivos o el esquema de la partición. De forma predeterminada, se crea un índice que no es único y no está agrupado si no se especifican las opciones únicas o agrupadas. Para crear un índice filtrado, use la cláusula opcional WHERE. Para obtener más información, vea Directrices generales para diseñar índices filtrados.
Crear el índice.

Un factor importante que debe tenerse en cuenta es si el índice se creará en una tabla vacía o en una tabla con datos. La creación de un índice en una tabla vacía no tiene implicaciones de rendimiento en el momento de creación del índice; sin embargo, el rendimiento se verá afectado cuando se agreguen los datos a la tabla.

La creación de índices en tablas grandes debe planearse con cuidado para que el rendimiento de la base de datos no se vea afectado. La mejor manera de crear índices en tablas de gran tamaño es empezar con el índice clúster y, a continuación, generar los índices no clúster. Considere la posibilidad de establecer la opción ONLINE en ON cuando cree índices en tablas existentes. Cuando se establece en ON, los bloqueos a largo plazo no se retienen, lo que permite que continúen consultas o actualizaciones a la tabla subyacente. Para obtener más información, vea Realizar operaciones de índices en línea.

Consideraciones de implementación

En la siguiente tabla se enumeran los valores máximos que se aplican a los índices clúster, no clúster, espaciales, filtrados y XML. A menos que se especifique lo contrario, las limitaciones se aplican a todos los tipos de índices.

Límites de índice máximos	Valor	Información adicional
Índices clúster por tabla	1
Índices no clúster por tabla	999	Incluye índices no clúster creados por restricciones PRIMARY KEY o UNIQUE e índices filtrados, pero no índices XML.
Índices XML por tabla	249	Incluye índices XML principales y secundarios en columnas de tipos de datos xml. Índices en columnas del tipo de datos XML
Índices espaciales por tabla	249	Trabajar con índices espaciales (motor de base de datos)
Número de columnas de clave por índice	16*	El índice clúster está limitado a 15 columnas si la tabla también contiene un índice XML principal o un índice espacial. Tamaño máximo de las claves de índices.
Tamaño del registro de clave de índice	900 bytes*	No se aplica a índices XML ni a índices espaciales. Para que una tabla admita el uso de índices espaciales, el tamaño máximo del registro de clave de índice es de 895 bytes. Tamaño máximo de las claves de índices.

*Puede evitar limitaciones de tamaño de registro y de columna de clave de índice de índices no clúster incluyendo columnas sin clave en el índice. Para obtener más información, vea Índice con columnas incluidas.

Tipos de datos

Generalmente, se puede indizar cualquier columna de una tabla o de una vista. En la siguiente tabla se muestran todos los tipos de datos que tienen una participación de índice restringida.

Tipo de datos	Participación de índice	Información adicional
Tipo definido por el usuario CLR	Se puede indizar si el tipo admite el orden binario.	Trabajar con tipos definidos por el usuario para CLR
Tipos de datos de objetos grandes (LOB): image, ntext, text, varchar(max), nvarchar(max), varbinary(max) y xml	No pueden ser una columna de clave de índice. No obstante, una columna XML puede ser una columna de clave en una tabla o en un índice XML secundario o principal. Pueden participar como columnas sin clave (incluidas) en un índice no clúster, excepto image, ntext y text. Pueden participar si son parte de una expresión de columna calculada.	Índice con columnas incluidas Índices en columnas del tipo de datos XML
Columnas calculadas	No se pueden indizar. Esto incluye columnas calculadas definidas como invocaciones de métodos de una columna del tipo definido por un usuario CLR, mientras los métodos se marquen como deterministas. Las columnas calculadas que se derivan de tipos de datos LOB se pueden indizar como columna con clave o sin clave mientras el tipo de datos de columna calculada se permita como columna de clave de índice o columna sin clave.	Crear índices en columnas calculadas
Columnas de Varchar de inserción no consecutiva	La clave de índice de un índice clúster no puede contener columnas varchar con datos existentes en la unidad de asignación ROW_OVERFLOW_DATA. Si un índice clúster se crea en una columna varchar y los datos existentes están en la unidad de asignación IN_ROW_DATA, las acciones de inserción o actualización posteriores de la columna que constituirían inserciones no consecutivas producirán un error.	Organización de tablas e índices Datos de desbordamiento de fila superiores a 8 KB
geometry	Se puede indizar con varios índices espaciales.	Tipos de datos espaciales

Consideraciones adicionales

A continuación se ofrecen algunas consideraciones adicionales para crear un índice:

Puede crear un índice si tiene el permiso CONTROL o ALTER en la tabla.
Cuando se crea, el índice se habilita automáticamente y está disponible para su uso. Puede quitar el acceso a un índice deshabilitándolo. Para obtener más información, vea Deshabilitar índices.

Requisitos de espacio en disco

El espacio en disco necesario para almacenar el índice depende de los siguientes factores:

El tamaño de cada fila de datos de la tabla y el número de filas por página. Así se determina el número de páginas de datos que se deben leer del disco para crear el índice.
Las columnas del índice y los tipos de datos utilizados. Así se determina el número de páginas de índice que se deben escribir en disco. Para obtener más información, vea Estimar el tamaño de un índice clúster y Estimar el tamaño de un índice no clúster.
Espacio temporal en disco necesario durante el proceso de creación del índice. Para obtener más información, vea Determinar requisitos de espacio en disco del índice.

Consideraciones de rendimiento

El tiempo que ocupa la creación física de un índice depende en gran medida del subsistema de disco. Los factores importantes que se deben tener en cuenta son:

El modelo de recuperación de la base de datos. El modelo de recuperación optimizado para cargas masivas de registros proporciona un rendimiento mucho mayor y un consumo de espacio de registro más reducido que la recuperación completa durante la operación de creación del índice. Sin embargo, la recuperación por medio de registros de operaciones masivas reduce la flexibilidad para la recuperación a un momento dado. Para obtener más información, vea Elegir un modelo de recuperación para las operaciones de índice.
RAID (matriz redundante de discos económicos) utilizada para almacenar los archivos de base de datos y del registro de transacciones. Normalmente, los niveles de RAID que utilizan la creación de bandas tienen un ancho de banda de E/S mejor.
Número de discos de la matriz de discos, si se utiliza RAID. Más unidades en la matriz aumentan las tasas de transferencia de datos proporcionalmente.
Dónde se almacenan las ordenaciones intermedias de los datos. Si utiliza la opción SORT_IN_TEMPDB puede reducir el tiempo necesario para crear un índice cuando tempdb se encuentra en un conjunto de discos diferente que la base de datos del usuario. Para obtener más información, vea tempdb y la creación de índices.
Creación del índice en línea o sin conexión.

Cuando se crea un índice sin conexión (valor predeterminado), los bloqueos exclusivos se mantienen en la tabla subyacente hasta que la transacción que crea el índice se ha completado. La tabla no está accesible para los usuarios mientras se crea el índice.

Excepto en el caso de los índices XML y los índices espaciales, es posible especificar que se cree el índice en línea. Cuando la opción en línea está establecida en ON, los bloqueos de la tabla a largo plazo no se conservan, lo que permite que las consultas o actualizaciones a la tabla subyacente continúen mientras se crea el índice. Aunque recomendamos operaciones de índice en línea, se debe evaluar el entorno y los requisitos específicos. Puede ser mejor ejecutar operaciones de índice sin conexión. Al hacerlo así, los usuarios tienen acceso restringido a los datos durante la operación, pero la operación acaba con mayor rapidez y utiliza menos recursos. Para obtener más información, vea Realizar operaciones de índices en línea.