Tema 10: Implementar índices

Introducción

Los índices son objetos de base de datos diseñados para mejorar el rendimiento de las consultas. En este punto veremos la estructura y el propósito de los índices y sus tipos y características. Se verá como determinar cuando un índice es necesario y apropiado, que tipo de índice usar y como crearlos. Una vez que se crean los índices se deben mantener para maximizar la performance de las consultas, para ello existen varias herramientas que asisten en la tarea de administración y mantenimiento de los índices. La administración comprende las tareas de reconstrucción, renombrado, y eliminación de índices.

Arquitectura de los índices

Los índices están estructurados para facilitar una respuesta rápida de conjuntos de resultados. Los dos tipos de índices que SQL Server soporta son agrupados y no agrupados. Los índices son aplicados  a una o más columnas en tablas o vistas. Tablas indexadas son soportadas por todas las ediciones de SQL Server 2000, y vistas indexadas son soportadas por las ediciones SQL Server Entreprise y SQL Server Developer. Las características de un índice afecta el uso de los recursos del sistema y performance general. El Query Optimizer usará un índice si este mejorará la performance de la consulta.

Propósito y estructura

Un índice en SQL Server asiste al motor de base de datos en la ubicación de los registros, tal como un índice en un libro ayuda a ubicar información rápidamente. Sin índices, una consulta deberá buscar en todos los registros de la tabla en orden a encontrar coincidencias. Un índice de base de datos una o más columnas de valores de la tabla (llamadas clave del índice) y punteros a los correspondientes registros de la tabla. Cuando se ejecuta una consulta usando la clave del índice, el Query Optimizer utilizará el índice para ubicar los registros que cumplen con la consulta.

Un índice es estructurado por el SQL Server Index manager como un árbol balanceado (B-tree). Un B-tree es análogo a un árbol invertido con la raíz del árbol arriba, y los niveles hoja abajo, con niveles medios entre ambos. Cada objeto en la estructura de árbol es un grupo de claves del índice ordenadas llamadas páginas del índice.

Un B-tree facilita realizar consultas rápidas y consistentes dado el cuidadoso balance del ancho y profundidad del árbol a medida que el índice crece. Ordenar el índice sobre la clave del mismo también mejora la performance. Todas las búsquedas comienzan en la raíz del B-tree y luego se mueven a través del árbol hasta el correspondiente nivel hoja. El número de registros de la tabla y el tamaño de la clave del índice afectan el ancho y profundidad del árbol. El tamaño de la clave del índice es llamado el ancho de la clave. Una tabla con muchos registros y un gran ancho de la clave crea un profundo y amplio B-tree. Cuanto menor sea el B-tree mas rápido retornará un valor buscado.

Para un rendimiento óptimo, se crean sobre columnas que son comúnmente usadas en las consultas. Por ejemplo, los usuarios pueden consultar la tabla de Clientes en base al apellido o al ID del cliente. Por lo tanto se deberían crear dos índices para la tabla: un índice por apellido y otro por ID del cliente. Para ubicar eficientemente a los registros, el Query Optimizer usa un índice que concuerde con la consulta. El Query Optimizer usará el índice por el ID del cliente cuando se ejecute la siguiente consulta:

SELECT * FROM Clientes WHERE Cliente_ID = 798

No cree índices para todas las columnas de una tabla, porque demasiados índices impactarán negativamente en la performance general. La mayoría de la bases de datos son dinámicas; esto es, regularmente los registros son agregados, eliminados y modificados. Cuando una tabla que contiene un índice es modificada, el índice debe ser actualizado para reflejar la modificación. Si la actualización del índice no se produjera, el índice se volvería inútil. Por lo tanto, las inserciones, eliminaciones y modificaciones de registros disparan al Index Manager para que actualice los índices de la tabla. Al igual que la tablas, los índices son estructuras que ocupan espacio en la base de datos. El espacio que ocupa un índice es directamente proporcional a la cantidad de registros en la tabla y al ancho de la clave del índice. Antes de crear un índice se debe realizar un balance que asegure que el incremento de performance por el aumento de las respuestas en la consultan justifica con creces la caída de rendimiento y la sobrecarga producida por la tarea de mantenimiento del índice.

Tipos de índices

Hay dos tipos de índices: agrupados (clustered) y no agrupados (non clustered). Ambos tipos de índices son estructuras tipo B-tree. Un índice agrupado contiene los registros de la tabla a nivel hoja del B-tree. Un índice no agrupado es una estructura de índice separada, independiente del ordenamiento físico de los registros en la tabla. Si existe un índice agrupado en un tabla, un índice no agrupado utilizará al índice agrupado para la búsqueda de los registros. En la mayoría de los casos se creará antes un índice agrupado que los índices no agrupados sobre una tabla.

Índices agrupados

Puede haber solo un índice agrupado por tabla o vista, dado que estos índices ordenan físicamente la tabla o vista según la clave del índice agrupado. Este tipo de índices es particularmente eficiente para consultas, dado que los registros de datos completos (en páginas de datos) son guardados a nivel de hoja del B-tree. El ordenamiento y la ubicación de los datos en un índice agrupado es análogo al de un diccionario donde las palabras son ordenadas en forma alfabética y las definiciones aparecen junto a las palabras.

Cuando se crea una restricción PRIMARY KEY en un tabla que no contiene un índice agrupado, SQL Server creará uno y utilizará la columna de clave primaria como clave para el índice agrupado. Si ya existe un índice agrupado SQL Server creará un índice no agrupado sobre la columna definida con una restricción PRIMARY KEY. Una columna definida como la clave primaria es un índice muy útil porque los valores de la columna están garantizados que son únicos. Índices sobre columnas de valores únicos son de menor tamaño que los índices sobre columnas con valores duplicados y generan estructuras de búsqueda más eficientes.

Una columna definida con una restricción UNIQUE genera automáticamente un índice no agrupado.

Para forzar el tipo de índice a ser creado para una columna o columnas, se puede especificar las cláusulas CLUSTERED o NONCLUSTERED en los comandos CREATE TABLE, ALTER TABLE o CREATE INDEX. Suponga que se crea una tabla Personas que contiene las siguientes columnas: PersonaID, Nombre, Apellido y NumDocumento. La columna PersonID se define con la restricción PRIMARY KEY, la columna NumDocumento con la restricción UNIQUE. Para hacer un índice agrupado para la columna NumDocumento y un índice no agrupado para la columna PersonID, se crea la tabla usando la siguiente sintaxis:

CREATE TABLE dbo.Personas
(
PersonID smallint PRIMARY KEY NONCLUSTERED,
Nombre varchar(39),
Apellido varchar(40),
NumDocumento char(11) UNIQUE CLUSTERED
)

Los índices no se limitan a las restricciones. Se pueden crear índices sobre cualquier columna o combinación de columnas en una tabla o vista. Índices agrupados aseguran la unicidad internamente. Por lo que, si se crea un índice agrupado sobre columnas con valores no únicos SQL Server crea un único valor sobre las columnas duplicadas para servir de clave de ordenamiento secundaria. Para evitar el trabajo adicional requerido para mantener valores únicos sobre columnas duplicadas, generalmente se generan índices agrupados sobre columnas con la restricción PRIMARY KEY.

Índices no agrupados

Sobre una tabla o vista se pueden crear 250 índice no agrupados o 249 índices no agrupados y un índice agrupado. Se debe primero crear un índice único agrupado sobre una vista previo a crear los índices no agrupados. Esta restricción no se aplica a las tablas. Un índice no agrupado es análogo a un índice al final de un libro. Se puede usar el índice del libro para ubicar las páginas que contienen una tema del índice del libro. La base de datos usa los índices no agrupados para encontrar registros según una clave.

Si no existe un índices agrupado para la tabla, los datos de la tabla se encontrarán desordenados físicamente y se dice que la tabla tendrá la estructura de montón (heap). Un índice no agrupado sobre una tabla montón contiene punteros a las filas de la tabla. Cada entrada en las páginas de índice contiene un identificador de fila (RID, row ID). El RID es un puntero a una fila en un montón, y este consiste de un número de página, un número de archivo y un número de ranura. Si existe un índice agrupado, las páginas de un índice no agrupado contienen las claves del índice agrupado en vez del RID.

Características de los índices

Una serie de características se pueden definir para los índices, además de si son o no agrupados, siendo las mas importantes:

·        Unicidad o no de los registros según la clave del índice.

·        Índices compuestos, formados por varias columnas.

·        Con un factor de llenado para permitir que las páginas crezcan como sea necesario.

·        Con un sentido de ordenamiento que especifique si será ascendente o descendente.

Con algunas limitaciones se pueden establecer índices sobre columnas computadas.

Unicidad

Cuando un índice es definido como UNIQUE, la clave del índice y sus correspondientes valores de la clave serán únicos. Un índice UNIQUE puede ser aplicado a cualquier columna si todos los valores de la columna son únicos. Un índice UNIQUE se puede definir sobre un conjunto de columnas mediante un índice compuesto. Por ejemplo , un índice UNIQUE puede ser definido sobre las columnas Apellido y NumDocumento, ninguna de ambas columnas deberá tener valores nulos y las combinaciones de los valores de ambas columnas para los registros deberán ser únicas.

SQL Server automáticamente crea un índice UNIQUE para una columna o columnas definidas con las restricciones PRIMARY KEY o UNIQUE. Por lo tanto, utilice solo las restricciones para forzar unicidad en vez de aplicar la característica UNIQUE  al índice. SQL Server no permite crear un índice UNIQUE sobre una columna que contenga valores de la clave repetidos.

Índices compuestos

Un índice compuesto es cualquier índice que use mas de una columna como clave. Los índices compuesto pueden mejorar el rendimiento de las consultas al reducir el número de operaciones de entrada/salida, porque  una consulta sobre una combinación de columnas contenidas en el índice será ubicada completamente en el índice. Cuando el resultado de una consulta se obtiene completamente desde el índice sin tener que consultar a los registros de la tabla , se dice que hay un recubrimiento de índice, esto tiene como resultado una extracción mas rápida de los datos, ya que solo se consultan las páginas del índice. Esto se produce cuando todas las columnas indicadas en las cláusulas SELECT y WHERE se encuentran dentro de la clave del índice o dentro de la clave del índice agrupado (si este existe). Recuerde que los valores de la clave del índice agrupado se encuentran también en las páginas de los índice no agrupados para poder encontrar los registros en la tabla.

Suponga que se ejecuta la siguiente consulta:

SELECT Emp_ID, Apel, NivelTrabajo
FROM Empleados01
WHERE Fecha_ingreso < (GETDATE() – 30)
         AND NivelTrabajo >= 100
ORDER BY NivelTrabajo

Si existe un índice agrupado sobre la columna Emp_ID y un índice no agrupado sobre las columnas Apel, NivelTrabajo y Fecha_Ingreso, luego habrá un recubrimiento de índice por parte del índice no agrupado, ya que contiene todas las columnas usadas en la consulta, por su propia clave y por tener indicados los registros a través de la clave del índice agrupado.

Factor de llenado

Cuando se inserta una fila en una tabla,SQL Server debe dispones de cierto espacio para ello. Un operación de inserción ocurre cuando se ejecuta un comando INSERT o cuando se ejecuta un comando UPDATE para actualizar una clave de un índice agrupado. Si la tabla no contiene un índice agrupado, el registro y la página del índice son colocados en cualquier espacio disponible en el montón. Si la tabla contiene un índice agrupado, SQL Server ubica el la página apropiada del índice dentro del B-tree y luego inserta el registro en el orden correspondiente. Si la página del índice se encuentra llena, esta es dividida (mitad de la página permanece en la página original y la otra mitad se mueve a una nueva página). Si la fila insertada es muy grande, podrían ser necesarias divisiones adicionales. Las divisiones de páginas son complejas e insumen recursos de manera intensiva. La divisiones de páginas mas comunes suceden en el nivel de las páginas hoja. Para reducir la ocurrencia de las divisiones de páginas se especifica cuánto se llenarán las páginas cuando se crea el índice. Este valor es llamado factor de llenado . Por defecto el factor de llenado vale cero, esto es que las páginas del índice serán llenadas cuando el índice se crea  sobre datos existente. Un factor de llenado de cero es lo mismo que un factor de llenado de 100. Se puede definir un valor global por defecto del factor de llenado utilizando el procedimiento almacenado sp_configure o asignarlo para un índice específico con la cláusula FILLFACTOR.

Sentido de ordenamiento

Cuando se crea un índice, este es ordenado de manera ascendente. Tanto los índices agrupados como los no-agrupados se ordenan, el índice agrupado representa el sentido de ordenamiento de la tabla. Considere el siguiente comando SELECT:

SELECT Emp_ID, Apel, NivelTrabajo
FROM Empleados01
WHERE Fecha_ingreso < (GETDATE() – 30)                AND NivelTrabajo >= 100

Fíjese, que no hay un sentido de ordenamiento especificado. Ya vimos que el Query Optimizer usa una índice compuesto para devolver un resultado para esta consulta. El índice compuesto es un índice no agrupado, y la primera columna es Apel. Supongamos que cuando se creó el índice no estableció tampoco un sentido de ordenamiento, por lo que el resultado es mostrado en orden ascendente comenzando con la columna Apel. La cláusula ORDER BY no ha sido indicada, para ahorrar recursos. Pero el resultado aparece ordenado por el apellido. El sentido de ordenamiento depende del incide utilizado para resolver la consulta (si no se especifica la cláusula ORDER BY o si no se indica explícitamente que índice utilizar). Si el Query Optimizer usa un índice agrupado para resolver la consulta, el resultado aparecerá en el orden establecido por ese índice, el cual es equivalente a las páginas de datos de la tabla. El siguiente comando Trnsact-SQL usa el índice agrupado sobre la columna Emp_ID para devolver un resultado en orden ascendente.

SELECT Emp_ID, Apel, Nombre FROM Empleados01

Información sobre índices

Para ver los índice y sus propiedades se pueden utilizar procedimientos almacenados del sistema, el Object Browser en el Query Analizer, o el Enterprise Manager. Conocer los índices aplicados a una tabla o vista ayuda a optimizar las consultas. Se puede analizar índices para diseñar comandos SELECT que retornen los resultados de manera eficiente., o se pueden crear nuevos índices para mejorar las consultas. Para ver los índices aplicados a una tabla o vista se puede utilizar los procedimientos almacenados del sistema sp_help sp_helpindex. Los siguientes comandos Transact-SQL muestra todos los índices creados para la tabla Empleados01

sp_helpindex Empleados01

El resultado que se retorna del sp_helpindex incluye el nombre del índice, el tipo de índice, el archivo de base de datos, y la o las columnas contenidas por el índice.

El Object Browser del Query Analizer provee similar información. En el Object Browser, expanda un nodo tabla y luego expanda el nodo Indexes. Luego, clic derecho sobre un índice en particular y seleccione Edit para mostrar la ventana de diálogo Edit Existing Index como se verá mas adelante.

Se pueden ver las propiedades de un índice y acceder al cuadro de diálogo Edit Existing Index desde un plan de ejecución. Clic derecho sobre un índice que aparezca en la pestaña del Execution Plan y seleccione Manage Indexes. Hecho esto se muestra el cuadro de diálogo Manage Index. Desde aquí se puede presionar el botón Edit  para mostrar el cuadro de diálogo Edit Existing Index. El cuadro de diálogo Manage Indexes esta también disponible en el Enterprise Manager. Primero ubique el nodo Tables para la base de datos en la consola del árbol. En el panel Details, clic derecho sobre una tabla, apuntar a All Task y por último clic en Manage Indexes. Se puede modificar, crear y borrar índices desde el cuadro de diálogo Manage Indexes, tal como veremos mas adelante.

Para ver todos los índices asignados en una base de datos, se puede consultar la tabla del sistema sysindexes en la base de datos. Por ejemplo, para consultar información sobre índices seleccionados en la base de datos Pubs, se ejecuta el siguiente código Transact-SQL:

USE Pubs
GO
SELECT name, rows, rowcnt, keycnt from sysindexes
WHERE name NOT LIKE ‘%sys%’
ORDER BY keycnt

Indexado Full-Text

El indexado Full-Text no es parte de las funciones de indexado descripta hasta ahora, pero se debe entender como este difiere del indexado provisto por el sistema SQL Server. UN índice Full-Text permite realizar consultas a texto completo para buscar datos en forma de cadenas de caracteres en la base de datos. Un índice Full-Text se guarda en un catálogo Full-Text. El motor Microsoft Search, no SQL Server, mantiene los índices y catálogos Full-Text.

Crear y administrar índices

Crear índices

Hay varios modos de crear un índice en SQL Server. Se puede crear una aplicación propia que use la interfase SQL-DMO para crear un índice. Como se vio se puede usar la opción Manage Indexes desde el Object Browser o accederlo desde un plan de ejecución en el Query Analizer. La opción Manage Indexes está también disponible desde el menú contextual de una tabla o vista en el Enterprise Manager. El Enterprise Manager ofrece además el asistente Create Index para crear índices paso a paso. Otro modo es crear un índice para una tabla utilizando el comando Transact-SQL CREATE INDEX. Por último, se pueden especificar las propiedades de una restricción de clave primaria o de una restricción de clave única durante la creación (CREATE TABÑE9 o modificación (ALTER TABLE) de una tabla.

Usando interfase gráfica

Desde el cuadro de diálogo Manage Indexes, clic el botón New para crear un índice y se muestra el cuadro de diálogo para acceder al cuadro de diálogo Create New Index.

Desde el cuadro de diálogo Create New Index, se puede proveer de un nombre al índice, el tipo de índice (agrupado o no agrupado), y de las propiedades del índice (unicidad,  factor de llenado, el grupo de archivos donde el índice deberá ser creado, etc.). Se puede además cambiar el orden de las columnas que son parte de una clave compuesta, seleccionando la columna y con clic en los botones Up y Down. La columna que está primera en la lista de columnas seleccionadas determinará el primer ordenamiento de la clave del índice. Fíjese que se puede especificar el orden descendiente para cualquier parte del índice. El Query Optimizer seleccionará el índice Productos que aparece en la figura cuando se ejecute el siguiente comando:

SELECT proveedorID, PrecioUnitario, NombreProducto
FROM Productos

El resultado muestra el ProveedorID en orden ascendente, seguido por el PrecioUnitario en orden descendiente. El índice ordena NombreProducto en orden ascendente, pero ese orden no aparece en el resultado porque ProveedorID y PrecioUnitario prevalecen al orden de la columna NombreProducto. Se muestran a continuación algunos registros del resultado para ilustrar este tema: 

ProductoID	PrecioUnitario	NombreProducto
1	19.0000	Chang
1	18.0000	Chai
1	10.0000	Aniseed Syrup
2	22.0000	Chef Anton's Cajun Seasoning
2	21.3500	Chef Anton's Gumbo Mix
2	21.0500	Louisiana Fiery Hot Pepper Sauce
2	17.0000	Louisiana Hot Spiced Okra
3	40.0000	Northwoods Cranberry Sauce
3	30.0000	Uncle Bob's Organic Dried Pears

Si se prefiere mas ayuda para crear índices se puede usar el asistente Create Index en el Enterprise Manager. El asistente Create Index está disponible en la opción Wizards del menú Tools. Haciendo clic en la opción Wizards se muestra el cuadro Select Dialog. En el cuadro Select Dialog, expanda Database, seleccione el asistente Create Index, y clic en Ok para comenzar el asistente. El asistente habilita para ver los índices ya creados sobre una tabla o vista y para crear nuevos índices seleccionando la columna (o columnas)  que deberían ser parte del índice, pudiendo además configurar las propiedades del índice.

Usar comandos Transact-SQL

Los comandos CREATE INDEX, CREATE TABLE y ALTER TABLE participan en la creación de los índices. Se puede crear un índice usando estos comandos Transact-SQL a través del Query Analizer o con una herramienta tal como osql.

Cuando se utiliza CREATE INDEX, se debe especificar el nombre del índice, la tabla o la vista, y la olas columnas sobre las que se aplicará el índice. Opcionalmente, se puede especificar si el índice deberá contener sólo valores no duplicados, el tipo de índice (agrupado o no), el sentido de ordenamiento para cada columna, propiedades del índice, y el grupo de archivos que lo contendrá. La configuración por defecto es la siguiente:

·        Se crean índices no agrupados

·        Se ordenan todas las columnas en un sentido descendente y se usa la base de datos actual para ordenar el índice.

·        Se usan las configuraciones globales del SQL Server para fijar el factor de llenado.

·        Se crean todos los ordenamientos resultantes durante ela creación del índice en el grupo de archivos por defecto

·        Actualiza estadísticas del índice

·        Deshace un proceso de múltiples inserciones si la condición de unicidad del índice es violada por alguno de los registros que están siendo ingresados.

·        Previene de ser sobrescrito a los índices existentes.-

Las principales cláusulas en un comando CREATE INDEX son resumidas como sigue:

CREATE
[UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX
nombre_indice  
ON [nombre_tabla | nombre_vista] (nombre_columna [,...n])
[WITH [propiedad_indice [,...n] ]
[ON grupo_archivos ]

Ya hemos aprendido el significado de esta cláusulas, cuales son opcionales y que configuraciones por defecto existen para cualquier cláusula no especificada en el comando CREATE INDEX. Resumiendo, las cláusulas UNIQUE y CLUSTERED o NONCLUSTERED son opcionales. Es también opcional el especificar las propiedades del índice a través de la cláusula WITH y especificar el grupo de archivos donde el índice será creado usando la cláusula ON.

El siguiente comando CREATE INDEX usa las configuraciones por defecto para todas las cláusulas opcionales:

CREATE INDEX Indice01 ON Tabla01(Columna01)

Un índice llamado Indice01 se crea sobre Tabla01. La clave del índice para la tabla será Columna01. El índice no tiene unicidad y no es agrupado. Todas las propiedades concuerdan con los valores por defecto de las base de datos.

El uso de cláusulas opcionales personaliza el comando CREATE INDEX siguiente:

CREATE UNIQUE CLUSTERED INDEX Indice01
ON Tabla01(Columna01, Columna03, DESC)  
WITH FILLFACTOR = 60
IGNORE_DUP_KEY, DROP_EXISTING,
SORT_IN_TEMPDB

Un índice llamado Indice01 reemplazará al índice existente del mismo nombre creado sobre la tabla Tabla01. La cláusula DROP_EXISTING indica que el índice Indice01 debe ser reemplazado. La clave del índice incluye a las columnas Columna01 y Columna03, haciendo de Indice01 un índice compuesto. La cláusula DESC configura el sentido de ordenación para la Columna03 como descendente (en vez de ascendente).  La cláusula FILLFACTOR establece que las páginas de nivel hoja del índice estén llenas en un 40% al crearse el índice, dejando libre un 60% del espacio para contener entradas adicionales. Las cláusulas CLUSTERED y UNIQUE configuran al índice como agrupado y sin valores duplicados; por lo que la tabla será físicamente ordenada por la clave del índice y los valores de la clave serán únicos. La palabra IGNORE_DUP_KEY habilita para que un proceso por lotes que contenga múltiple comandos INSERT sea exitoso al ignorar cualquier INSERT que viole el requerimiento de unicidad.. La palabra SORT_IN_TEMDB indica al índice que efectúe las operaciones de ordenamientos intermedios en TempDB. Esta cláusula se usa típicamente para mejorar la velocidad a la que se crea o reconstruye un índice grande o para disminuir la fragmentación del índice. Dado que una segunda cláusula ON no se ha puesto el índice será creado en el grupo de archivos por defecto de la base de datos.

Crear una restricción PRIMARY KEY o UNIQUE automáticamente crea un índice. Como se vio, estas restricciones se definen cuando se crea o modifica una tabla. Los comandos CREATE TABLE y ALTER TABLE incluyen configuraciones para los índices por lo que se puede personalizar a los índices que se crean con estas restricciones.

Las principales cláusulas en el comando CREATE TABLE que se relacionan con la creación de índices son:

CREATE TABLE nombre_tabla
( nombre_columna tipo_dato  
CONSTRAINT nombre_restriccion[PRIMARY KEY | UNIQUE]
[CLUSTERED | NONCLUSTERED]
[WITH FILLFACTOR = factor_llenado]
[ON grupo_archivo] )

una restricción e clave primaria esta siempre configurada NOT NULL. Se puede especificar NOT NULL pero está implícita en la definición de la restricción PRIMARY KEY. El siguiente comando CREATE TABLE usa configuraciones por defecto en la definición de una restricción PRIMARY KEY cuando crea una tabla con restricción de clave principal.

CREATE TABLE Tabla01 (Columna01 int
CONSTRAINT pk_columna01
PRIMARY KEY)

Una tabla llamada Tabla01 es creada con una sola columna llamada Columna01. la cláusula PRIMARY KEY define a Columna01 con una restricción de clave principal llamada pk_columna01, que es un índice agrupado con valores únicos de clave por defecto.

El uso de cláusulas opcionales para la creación de índices personaliza el siguiente comando CREATE TABLE:

CREATE TABLE Tabla01 (Columna01 int
CONSTRAINT pk_columna01
PRIMARY KEY
WITH FILLFACTOR = 50
ON SECONDARY)

La sintaxis de ALTER TABLE para crear o modificar restricciones PRIMARY KEY o UNIQUE es similar a la del comando CREATE TABLE. En el comando ALTER TABLE, s debe especificar si se está modificando, agregando o eliminando una restricción. Por ejemplo, el siguiente comando ALTER TABLE agrega una columna una restricción UNIQUE para la tabla Tabla01:

ALTER TABLE tabla01 ADD Columna02 int
CONSTRAINT uk_columna02
UNIQUE

La restricción de unicidad se llama uk_columna02 y es un índice no agrupado. Una restricción de unicidad crea un índice no agrupado salvo que se especifique la cláusula CLUSTERED y que no exista previamente un ningún índice agrupado.

Administrar índices

Las tareas de mantenimiento de índices incluyen reconstrucción, eliminación, y renombrado. Un índice se elimina si no se lo va a utilizar más o si esta corrupto. Se reconstruye para la mantener un factor de llenado personalizado o para reorganizar el almacenamiento de los datos del índice para eliminar su fragmentación. Los índices se renombran si cambió la convención de nombres adoptada o si existen índices que no respetan la convención de nombres.

Eliminar un índice

Los índices en desuso de tablas que son frecuentemente actualizadas con nueva información deberían ser removidos. En caso contrario, SQL Server desperdiciaría recursos en mantener índices en desuso. Use la siguiente sintaxis para eliminar un índice:

DROP INDEX nombre_tabla.nombre_indice | nombre_vista.nombre_indice

El nombre de la tabla o de la vista debe ser incluido en el comando DROP INDEX. Se pueden eliminar varios índices con un solo comando DROP INDEX. El siguiente comando borra un índice de una tabla y uno de una vista:

DROP INDEX Tabla01.Indice01, Vista01.Indice02

Se puede eliminar un índice usando el Object Browser en el Query Analizer o utilizando el Entreprise Manager.

Reconstruir un índice

Si existe un índice agrupado sobre una tabla o una vista, cualquier índice no agrupado sobre la misma tabla o vista usará el índice agrupado y su clave. Si se elimina el índice agrupado utilizando el comando DROP INDEX se provocará que todos los índices no agrupados sean reconstruidos para que utilicen el RID (en vez de la clave del índice). Si un índice agrupado se recrea usando el comando CRETE INDEX provoca que todos los índices no agrupados sean reconstruidos utilizando para acceder a cada registro la clave del nuevo índice agrupado en vez del RID. Para tablas o vista grandes con varios índices, este proceso de reconstrucción puede consumir bastantes recursos. Afortunadamente existen otros recursos para reconstruir un índice que eliminarlo y volverlo a crear. Utilizando el comando DBCC DBREINDEX o especificando la cláusula  DROP_EXISTING en el comando CREATE TABLE.

El comando DBCC DBREINDEX reconstruye, a través de un solo comando, uno o más índices sobre una tabla o vista. Esta capacidad evita tener que utilizar múltiples comandos DROP INDEX y CREATE INDEX para reconstruir múltiples índices. Para reconstruir todos los índices, utilice el comando DBCC DBREINDEX para reconstruir el índice agrupado y por lo tanto, se procederá a la reconstrucción de todos los índices en la tabla o vista.  Si se usa el comando DBCC DBREINDEX sin indicar ningún índice se reconstruirán todos los índices de la tabla o vista. El comando DBCC DBREINDEX es especialmente útil para índices creados por las restricciones de clave primaria y de unicidad, porque a diferencia de DROP INDEX, no es necesario borrar la restricción antes de reconstruir el índice. Por ejemplo, el siguiente comando fallará al borrar un índice sobre una restricción de clave primaria llamada pk_Columna01:

DROP INDEX Tabla01.pk_columna01

Sin embargo, el siguiente comando DBCC DBREINDEX reconstruirá el índice para la restricción de clave primaria:

DBCC DBREINDEX (Tabla01.pk_columna01, 60)

El índice pk_columna01 soblre la restricción de clave primaria pk_columna01 es reconstruido con un factor de llenado del 60 por ciento. DBCC DBREINDEX es comúnmente utilizado para reestablecer la configuración del factor de llenado sobre los índices a fin de bajar la frecuencia de división de las páginas del índice.

La cláusula DROP_EXISTING de un comando CREATE INDEX reemplaza un índice con el mismo nombre de una tabla o vista. Como resultado, el índice es reconstruido, la cláusula DROP_EXISTING provee de mayor eficiencia al proceso de reconstrucción del índice, mas que DBCC DBREINDEX,. Si se utiliza el comando CREATE INDEX con la cláusula DROP_EXISTING para reemplazar un índice agrupado con idéntica clave de índice, los índices no agrupados no son reconstruidos y la tabla no es reordenada. Si se cambia la clave del índice agrupado los índices no agrupados son reconstruidos y la tabla reordenada.

Renombrar un índice

Se puede renombrar un índice eliminándolo y recreándolo. Una forma mas simple de renombrar un índice, sin embargo, es usar el procedimiento almacenado del sistema sp_rename. El siguiente ejemplo muestra como renombrar un índice llamado indice01 por indice02.

sp_rename @objname = ‘Tabla01.indice01’ , @newname = ‘indice02’,

@objtype = ‘INDEX’

El nombre de la tabla fue incluido en el parámetro de entrada @objname. Si no se indica el nombre de la tabla en dicho parámetro, el procedimiento almacenado no podría encontrar al índice para renombrarlo. Sin embargo, el nombre de la tabla fue intencionalmente excluido del parámetro @newname, ya que si se lo incluyera el nuevo nombre del índice incluiría el nombre de la tabla. Por ejemplo, si se especifica @newname = ‘Tabla01.indice02’ el índice se llamaría Tabla01.indice02 en vez de indice02. El nombre de la tabla es innecesario en el parámetro @newname porque lo toma de parámetro @objname. El parámetro de entrada @objtype debe ser configurado como ‘INDEX’ o el procedimiento almacenado será incapaz de ubicar el tipo de objeto correcto a ser renombrado.

Elegir un índice

Esta sección provee lineamientos adicionales para determinar cuando crear un índice y decidir que propiedades del índice configurar para un óptimo rendimiento. Tenga en cuenta que solamente un índice agrupado es permitido por tabla o vista. Por lo que un diseño cuidadoso del índice no agrupado será mas importante que el diseño de los índices no agrupados.

Se crean índices de acuerdo a los tipos de consultas que los usuarios comúnmente ejecutan contra la base de datos. El Query Optimizer luego selecciona uno o mas índices para realizar la consulta. Los siguientes tipos de consultas, separadas o en combinación se benefician de los índices:

Índices agrupados

Puesto que los datos están ordenados físicamente según una clave agrupada, realizar búsquedas mediante un índice agrupado es casi siempre más rápidos que realizarlas mediante un índice no agrupado. Puesto que sólo se permite crear un índice agrupado por tabla, selecciones dicho índice de manera juiciosa. Las siguientes reglas le ayudarán a determinar cuándo elegir un índice agrupado:

·        Columnas en las que el índice tenga pocos valores distintos. Puesto que los datos están físicamente ordenados, todos los valores duplicados se mantienen agrupados. Cualquier consulta que trate de extraer registros con tales claves encontrará todos los valores con un número mínimo de operaciones de E/S.

·        Columnas que suelan ser especificadas en la cláusula ORDER BY. Puesto que los datos ya están ordenados, SQL Server no tiene que volverlos a ordenar.

·        Columnas en las que se suelan realizar búsquedas de rangos de valores. Puesto que la página hoja de un índice agrupado es, en realidad una página de datos, los punteros de un índice agrupado hacen referencia a las páginas en las que los datos residen. SQL Server puede usar este índice para localizar las páginas inicial y final del rango especificado, lo que permite una más rápida exploración del rango.

·        Columnas que sean usadas más frecuentemente en la cláusula JOIN.

·        Consultas que puedan devolver grandes conjuntos de resultados con valores de clave adyacentes.

Índices no agrupados

Recuerde siempre que, a medida que añada mas índices al sistema, las instrucciones de modificación de datos se harán mas lentas.

Las siguientes reglas le ayudarán a elegir el índice no agrupado correcto para su entorno:

·        Columnas que tengan un gran número de valores diferentes o consultas que devuelvan conjuntos de resultados pequeños. Puesto que las páginas hojas de un índice no agrupado contienen punteros al identificador de la fila de la página de datos. SQL Server puede utilizar un índice no agrupado para acceder de forma bastante eficiente a los registro individuales.

·        Consultas que empleen columnas indexadas en las cláusulas WHERE y ORDER BY Si el Query Optimizer selecciona un índice no agrupado, el orden de los valores de clave en el árbol binario será el mismo que las columnas especificadas en la cláusula ORDER BY. En tales casos, SQL Server puede prescindir de crear una tabla de trabajo temporal interna para realizar la ordenación de los datos. La siguiente consulta es un ejemplo de situación en la que SQL Server evita el paso adicional de crear una tabla de trabajo para una ordenación:

SELECT * FROM Autores WHERE Estado LIKE “c%”
ORDER BY Estado

Recubrimiento de índice

El recubrimiento de índice es una situación en que todas las columnas de las cláusulas SELECT  y WHERE de la consulta forman también parte del índice no agrupado o de la clave del índice agrupado (si es que existe). Esto tiene como resultado una consulta mucho más rápida porque toda la información puede provenir directamente de la página índice y SQL Server evita realizar accesos a las páginas de datos. El siguiente ejemplo tiene n índice no agrupado sobre las columnas Autor_Apellido y Autor_Nombre de la tabla Autores

SELECT Autor_Apellido, Autor_Nombre
FROM Autores
WHERE Autor_Apellido LIKE ‘M%’

Mucha otras consultas que utilicen una cláusula de agregación (como MIN, MAX, AVG, etc)  o que comprueben la existencia de un criterio también se benefician del recubrimiento de índice. La consulta siguiente es un ejemoplo de recubrimiento de índice mediante agregados:

SELECT COUNT(Autor_Apellido) FROM Autores WHERE Autor_Apellido LILE ‘M%’

Índices compuesto frente a índices múltiples

A medida que la clave se hace más ancha, la selectividad de la misma se hace también mejor. Pudiera parecer, por tanto, que crear índices anchos da como resultado un mejor rendimiento, pero eso no es cierto de manera general. La razón es que, cuanto más ancha sea la clave, menos filas puede almacenar SQL Server en la páginas de índice, haciendo que haya un mayor número de niveles de árbol binario; como consecuencia, para llegar a una fila específica. SQL Server debe realizar más operaciones de E/S. Para obtener un mejor rendimiento de las consultas, cree múltiples índices estrechos, en lugar de unos pocos anchos. La ventaja es que con claves más pequeñas, el optimizador puede explorar rápidamente múltiples índices para crear el plan de acceso más eficiente. Asimismo, al disponer de más índices, el optimizador puede elegir entre varias alternativas. Si está tratando de determinar si usar una clave ancha, compruebe la distribución individual de cada miembro de la clave compuesta. Para ello utilice el valor de la selectividad que es el cociente entre la cantidad de registros distintos de la clave sobre el total de registros de la tabla y configura la inversa de la densidad de la clave  Si la selectividad de la columnas individuales es muy buena (mayor al 70%), considere partir el índice en múltiples índices. Si la selectividad de las columnas individuales es mala, pero es buena para las columnas combinadas, tiene sentido disponer claves más anchas en una tabla. Para obtener la combinación correcta, llene la tabla con datos tomados del mundo real, experimente creando múltiples índices y compruebe la distribución de cada columna. Basándose en los pasos de distribución y en la densidad de índice podrá tomar la decisión que mejor funcione para su entorno.