Configurar opciones de índice

Al diseñar, crear o modificar un índice debe tener en cuenta varias opciones de índice. Estas opciones se pueden especificar cuando crea un índice por primera vez o cuando vuelve a generar un índice. Además, algunas opciones de índice también se pueden establecer en cualquier momento mediante la cláusula SET de la instrucción ALTER INDEX.

Opción de índice

Descripción

Valor almacenado en metadatos

Tema relacionado

PAD_INDEX

Establece el porcentaje de espacio disponible en las páginas de nivel intermedio durante la creación del índice.

Factor de relleno

FILLFACTOR

Establece el porcentaje de espacio disponible en el nivel hoja de cada página de índice durante la creación del índice.

Factor de relleno

SORT_IN_TEMPDB

Determina la ubicación en la que se almacenan los resultados de ordenación intermedia generados durante la creación del índice.

Cuando está establecido en ON, los resultados de ordenación se almacenan en tempdb. Cuando está establecido en OFF, los resultados de ordenación se almacenan en el grupo de archivos o el esquema de particiones en el que se almacena el índice resultante.

NotaNota
Si no se necesita una operación de ordenación, o si la ordenación se puede realizar en memoria, SORT_IN_TEMPDB se omite.

No

tempdb y la creación de índices

IGNORE_DUP_KEY

Especifica la respuesta de error cuando una operación de inserción intenta insertar valores de clave duplicados en un índice único. La opción IGNORE_DUP_KEY se aplica solamente a operaciones de inserción realizadas tras crear o volver a generar el índice. El valor predeterminado es OFF.

CREATE INDEX

STATISTICS_NORECOMPUTE

Especifica si las estadísticas de índices obsoletas deben calcularse de nuevo automáticamente.

Estadísticas de índice

DROP_EXISTING

Indica que el índice existente debería eliminarse y crearse de nuevo.

No

Reorganizar y volver a genera índices

ONLINE

Determina si se permite el acceso simultáneo de usuarios a la tabla subyacente o a los datos del índice clúster y cualquier índice no clúster asociado durante las operaciones de índice.

NotaNota
Las operaciones de índices en línea únicamente están disponibles en las ediciones Enterprise, Developer y Evaluation de SQL Server.

No

Realizar operaciones de índice en línea.

ALLOW_ROW_LOCKS

Determina si se utilizan bloqueos de fila al obtener acceso a los datos de índice.

CREATE INDEX

Personalizar el bloqueo de un índice

ALLOW_PAGE_LOCKS

Determina si se utilizan bloqueos de página al obtener acceso a los datos de índice.

CREATE INDEX

Personalizar el bloqueo de un índice

MAXDOP

Establece el número máximo de procesadores que el procesador de consultas puede utilizar para ejecutar una sola instrucción de índice. Pueden utilizarse menos procesadores dependiendo de la carga de trabajo actual del sistema.

NotaNota
Las operaciones de índices en paralelo únicamente están disponibles en las ediciones Enterprise, Developer y Evaluation de SQL Server.

No

Modificar índices

DATA_COMPRESSION

Especifica la opción de compresión de datos para la tabla, el número de partición o el intervalo de particiones especificado. Las opciones son NONE, ROW y PAGE.

Crear tablas e índices comprimidos

Para establecer opciones en un índice

Establecer opciones sin volver a generar

Mediante la cláusula SET en la instrucción ALTER INDEX, puede establecer las siguientes opciones del índice sin volver a generarlo:

  • ALLOW_PAGE_LOCKS

  • ALLOW_ROW_LOCKS

  • IGNORE_DUP_KEY

  • STATISTICS_NORECOMPUTE

Estas opciones se aplican inmediatamente al índice. Otras opciones de índice, como FILLFACTOR y ONLINE, sólo pueden especificarse cuando se crea o vuelve a generar un índice.

Ver la configuración de opciones de índice

No todos los valores de las opciones de índice se almacenan en metadatos. Los valores que se almacenan en metadatos pueden verse en las vistas de catálogo correspondientes. Para examinar la configuración actual de las opciones en los índices existentes, utilice la vista de catálogo sys.indexes. Para examinar el valor actual de STATISTICS_NORECOMPUTE, utilice la vista de catálogo sys.stats. Para obtener más información, vea Ver información de índice.

Ejemplos

El siguiente ejemplo establece las opciones ALLOW_ROW_LOCKS y IGNORE_DUP_KEY para el índice AK_Product_ProductNumber en la tabla Production.Product.

USE AdventureWorks2008R2;
GO
--Verify the current values for these options.
SELECT allow_row_locks, ignore_dup_key
FROM sys.indexes 
WHERE name = N'AK_Product_ProductNumber';
GO
--Set the ALLOW_ROW_LOCKS option OFF and the IGNORE_DUP_KEY option ON.
ALTER INDEX AK_Product_ProductNumber       
ON Production.Product       
SET (ALLOW_ROW_LOCKS = OFF, IGNORE_DUP_KEY = ON);
GO
--Verify the new values for these options.
SELECT allow_row_locks, ignore_dup_key
FROM sys.indexes 
WHERE name = N'AK_Product_ProductNumber';
GO