Crear índices (motor de base de datos)SQL Server 2008 R2Otras versiones

Personas que lo han encontrado útil: 4 de 7 - Valorar este tema

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 índicesLas siguientes tareas forman parte de la estrategia recomendada para crear índices:

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

2. Determinar el mejor método de creación. Los índices se crean de las siguientes maneras:

o Definiendo una restricción PRIMARY KEY o UNIQUE en una columna mediante CREATE TABLE o ALTER TABLESQL 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.

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

3. 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ónEn 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Í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 datosGeneralmente, 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

Tipo definido por el usuario CLR Se puede indizar si el tipo admite el orden binario.

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 columnacolumna 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, ntextPueden participar si son parte de una expresión de columna calculada.

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.

Columnas de Varchar de inserción no consecutiva La clave de índice de un índice clúster no puede contener columnasexistentes en la unidad de asignación ROW_OVERFLOW_DATA. Si un índice clúster se crea en una columna

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.

geometry Se puede indizar con varios índices espaciales.

Consideraciones adicionalesA 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 discoEl 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 rendimientoEl 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 cuandotempdb 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.

Para crear una restricción PRIMARY KEY o UNIQUE al crear una tabla CREATE TABLE

Para crear una restricción PRIMARY KEY o UNIQUE en una tabla existente ALTER TABLE

Para crear un índice CREATE INDEX CREATE SPATIAL INDEX (Transact-SQL) CREATE XML INDEX (Transact-SQL) Cómo crear un índice espacial (SQL Server Management Studio)