Operaciones de Índices SQL Server

En los artículos previos de estas series, hemos descrito la estructura de las tablas e índices de SQL Server, los conceptos principales que son usados para describir el índice, lo básico y las líneas guía que son usadas para diseñar el índice apropiado. En este artículo, iremos a través de las operaciones que pueden ser realizadas en los índices de SQL Server.

Creando Índices

Antes de crear un índice, es mejor seguir las líneas guía de diseño del índice y mejores prácticas que son descritas en el artículo previo, para determinar las columnas que van a participar en el índice, el tipo de índice creado, las opciones de índice apropiadas, como el FillFactor, o Sort en TempDB y la localización de almacenaje de ese índice.

El índice SQL Server puede ser creado usando la declaración CREATE INDEX T-SQL o de la caja de diálogo New Index usando la herramienta de SQL Server Management Studio, al proveer:

El nombre del índice,
El tipo de índice,
La singularidad de los valores índice clave,
El nombre de la tabla en el cual el índice será creado,
La lista de columnas que participarán en ese índice
Y diferentes opciones de índice como el FillFactor, Sort en TempDB, dejando los índices similares
Y los grupos de archivos de índice y localización

La sintaxis CREATE INDEX T-SQL de abajo es usada como una plantilla para crear un nuevo índice SQL Server. La ausencia de la opción UNIQUE no forzará la singularidad de los valores de la clave índice. En un índice singular, ningunas dos filas están permitidas de tener el mismo valor de clave de índice. Si el tipo de índice no es especificado en la declaración CREATE INDEX T-SQL, un índice No Agrupado será creado.

CREATE [UNIQUE] [ CLUSTERED | NONCLUSTERED ] INDEX [index_name] ON [TableName]

(

[Column1] ASC,

[Column2] ASC

) ON PRIMARY

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

La misma operación puede ser realizada usando el SQL Server Management Studio, al navegar en la tabla donde necesitas crear un índice, clic derecho en los nodos de índices y escoge la opción New Index, de la cual puedes especificar el tipo de índice a ser creado como se muestra abajo:

El nombre de la tabla será llenado automáticamente de la pestaña General del cuadro de diálogo de New Index mostrado, con un nombre sugerido editable del índice. El tipo de índice será también llenado automáticamente de la lista de tipos provista, con la posibilidad de especificar la singularidad de ese índice y la lista de columnas que serán añadidas a esa lista y el orden de cada columna, como se muestra claramente abajo:

En la tabla de Opciones del cuadro de diálogo de New Index, puedes cambiar los valores por defecto de las diferentes opciones de creación de índice, igual que las especificadas en la cláusula WITH de la declaración CREATE INDEX T-SQL, como se muestra abajo:

La localización de la configuración del almacenaje del índice, como el grupo de archivo y el esquema de partición en los cuales el índice será almacenado, puede ser especificada en la pestaña Storage del cuadro de diálogo New Index, como se muestra abajo:

De la pestaña Filter del cuadro de diálogo New Index, puedes especificar la condición WHERE del índice filtrado, para seleccionar la información para ser almacenada en un índice filtrado creado, como se muestra abajo:

Los índices SQL Server pueden ser creados indirectamente al definir las restricciones PRIMARY KEY y UNIQUE dentro de las declaraciones CREATE TABLE o ALTER TABLE. SQL Server creará un índice agrupado único para aplicar la restricción PRIMARY KEY a menos que definas un índice agrupado en esa tabla. Recuerda que no podemos crear más de un índice agrupado por cada tabla. Un índice único no agrupado será creado automáticamente para aplicar la restricción UNIQUE. Deberías obtener permisos de la tabla CONTROL o ALTER para poder crear un índice.

Asume que planeamos crear la tabla de abajo que contiene dos columnas, la columna de ID como PRIMARY KEY y la columna STD Name como UNIQUE, usando la declaración CREATE Table T-SQL de abajo:

USE SQLShackDemo

CREATE TABLE IndexDemo

( ID INT IDENTITY (1,1) PRIMARY KEY,

STD_Name NVARCHAR(50) NOT NULL UNIQUE

)

Viendo la tabla creada del explorador objeto del SQL Server Management Studio, verás que dos nuevos índices serán creados en esa tabla automáticamente, sin proveer ninguna declaración CREATE INDEX. Un índice agrupado será creado para aplicar la restricción PRIMARY KEY y un índice único No Agrupado será creado para aplicar la restricción UNIQUE, como se muestra abajo:

Crear nuevos índices en largas tablas debería ser planeado cuidadosamente, debido al impacto de rendimiento de este proceso en un ambiente de producción. Necesitas planificar cuidadosamente empezando de la creación de índices agrupados, después de que puedas crear los índices No Agrupados sobre ellos.

También, ajustando la opción ONLINE a ON cuando creas el índice, habilitará la recuperación de otra información o procesos de modificación en la tabla subyacente para continuar prevenir el proceso de creación de índices de fijar la tabla. Por otro lado, el proceso de creación de índice ONLINE tomará más largo tiempo que el proceso de creación por defecto offline.

Usando la opción SORT_IN_TEMPDB puede ayudar a reducir el tiempo requerido para crear el índice, tomando en consideración que el tempdb es recibido en una unidad de disco separada que la base de datos del usuario.

SQL Server nos ayuda a crear hasta un índice agrupado, 999 índices no agrupados, 249 índices XML, 249 índices Espaciales en cada tabla. Puedes añadir hasta 16 columnas clave por índice, con tamaño máximo permitido para un registro de índice clave igual a 900 bytes. No es permitido añadir columnas con tipos de información image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml al índice clave. El número, tamaño de registro y limitación de tipo de información de las columnas de índice clave pueden ser evadidas al incluir las columnas No-Clave en los índices, mientras vamos a ver en detalle en el artículo Diseño efectivo de índices no agrupados.

Deshabilitando Índices

Cuando desactivas el índice SQL Server, la definición, estadísticas, y la información de ese índice no será removidos del catálogo del sistema, pero no podrás acceder a ese índice. Desactivando el índice No agrupado te prevendrá de acceder solo a ese índice. Por otro lado desactivando el índice agrupado te prevendrá de acceder a la información de la tabla subyacente, hasta que dejes o reconstruyas el índice.

Puedes decidir de desactivar un índice temporalmente debido a muchas causas, como solucionar problemas de un escenario específico, corrigiendo un error de disco I/O antes de reconstruir el índice, o eliminando el espacio del disco temporario requerido para almacenar la antigua y nueva versión del índice durante el proceso de reconstrucción de índice, donde solo 20 por ciento del tamaño del índice será requerido para clasificar la información del índice

El Motor de SQL Server desactiva índices de base de datos que puedan contener una expresión, objetos de base datos o colaciones que puedan ser cambiadas o puedan tener conflicto con un cambio en el proceso de mejoramiento, automáticamente cuando se realiza un proceso de mejoramiento a una nueva edición de Service Pack para SQL Server, y reconstruye automáticamente una vez que el proceso de mejoramiento es completado exitosamente.

Un índice puede ser desactivado manualmente usando la declaración ALTER INDEX DISABLE T-SQL. En la tabla IndexDemo previamente creada, si planeamos desactivar el índice No-agrupado, que es usado para aplicar la singularidad de la columna STD Name, usando la declaración ALTER INDEX DISABLE T-SQL, y ver el plan de ejecución de la declaración simple SELECT antes y después de desactivar el índice: