Crear índices (motor de base de datos)

  • Artículo

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:

  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:
    • Definiendo una restricción PRIMARY KEY o UNIQUE en una columna mediante CREATE TABLE o ALTER TABLE
      SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) crea automáticamente un índice exclusivo para hacer cumplir la unicidad de los requisitos de una restricción PRIMARY KEY o UNIQUE. De forma predeterminada se crea un índice agrupado único para hacer cumplir una restricción PRIMARY KEY, a menos que ya exista un índice agrupado en la tabla o que usted especifique un índice no agrupado único. De forma predeterminada se crea un índice único no agrupado para hacer cumplir una restricción UNIQUE a menos que se especifique de explícitamente un índice agrupado único y no exista un índice agrupado 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.
  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 agrupado y, a continuación, generar los índices no agrupados. 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 índices agrupados, no agrupados 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 agrupados por tabla

1

 

Índices no agrupados por tabla

249

Incluye índices no agrupados creados por restricciones PRIMARY KEY o UNIQUE, 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 de tipo de datos xml

Número de columnas de clave por índice

16*

Tamaño máximo de las claves de índices.

El índice agrupado está limitado a 15 columnas si la tabla también contiene un índice XML principal.

Tamaño máximo del registro de clave de índice

900 bytes*

No pertenece a índices XML.

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 agrupados 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 agrupado, excepto image, ntext y text.

Pueden participar si son parte de una expresión de columna calculada.

Índice con columnas incluidas

Índices en columnas de 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 agrupado no puede contener columnas varchar con datos existentes en la unidad de asignación ROW_OVERFLOW_DATA. Si un índice agrupado 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

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:

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 de registro masivo 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 de 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 con 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.
    En SQL Server 2005, puede especificar que se cree el índice con conexión. Cuando la opción con conexión 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 con conexión, 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

Vea también

Conceptos

Crear índices agrupados
Crear índices con columnas incluidas
Crear índices no agrupados
Crear índices únicos
Implementar tablas e índices con particiones
Restricciones PRIMARY KEY
Restricciones UNIQUE
Índices en columnas de tipo de datos xml

Otros recursos

ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)
Introducción a la búsqueda de texto

Ayuda e información

Obtener ayuda sobre SQL Server 2005