Tipos de índices

Antes que nada es importante conocer la manera en que SQL Server almacena la información y como la accesa. Uno de los terminos más utilizados es el concepto de "Heap". En realidad "Heap" se refiere a una coleccion de páginas de datos que almacenan registros para una tabla, en donde se cumple lo siguiente:

• Cada página de datos consta de 8KB de información y cada grupo de 8 páginas de le conoce como extent.
• Los registros no son almacenados en un orden en particular dentro de las páginas de datos y no existe un orden para las páginas de datos.
• Las páginas de datos no son ligadas mediante alguna lista
• Cuando un registro es insertado en una página y la página se llena, se crea un page split.

Como se accesa a la información
SQL Server accesa a los datos de dos maneras diferentes.

Mediante un table scan, en donde se revisa cada una de las páginas de datos que conforman a una tabla. Este tipo de accesos realiza lo siguiente:

• Comienza en la primera página de la tabla
• Revisa página por página pasando por todos los registros de la tabla.
• Extrae los registros que cumplen con el criterio del query

Mediante un indice
Cuando SQL Server utiliza un indice se llevan a cabo los siguientes pasos:

• Accesa a la estructura del indice para encontrar los registros que el query requiere.
• Extrae solamente los registros necesarios que cumplen con el criterio del query

SQL Server primero determina si la tabla tiene indices o no, después el optimizador de consultas que s el componente responsable de generar el mejor plan de ejecución, determina si es más eficiente realizar un table scan o utilizar un índice para accesar a los datos.

Antes de generar un indices se debe de considerar lo siguiente:

• Los índices generalmente aceleran las consultas que realizan joins entre tablas, sentencias de sort y de agrupación.
• Los índices forzan la unicidad de datos si es especificado en la sentencia del ínidice.
• Los índices se crean y mantienen en orden acendente o descendente
• Los índices funcionan mucho mejor si son creados sobre columnas con un alto grado de selectividad, esto significa que la mayoría de los datos de la columna sean unicos.

Cuando no debe de crearse un índice
Debe de tenerse en mente que aunque los indices son muy útiles para mejor el performance de consultas, estos consumen espacio en disco e incrementan el overhead y costo de mantenimiento. Tenga en cuenta lo siguientes puntos al crear un índice.

• Cuando modifique datos en una columna que se encuentra dentro de un índice, SQL Server también actualiza el índice.
• Dar mantenimiento a los índices requieres de tiempo y recursos. Por esa razón no cree indices que no va a utilizar con frecuencia.
• Los índices sobre columnas que contienen mucha información duplicada nos dan pocos beneficios, es mejor no crear un indice en este tipo de columnas.

Existen dos tipos de índices, los índices clustered que modifican el orden en que se almacenan los datos físicamente, esto es se ordenan por el índice generado y los índices nonclustered que no modifican el orden de los datos.

Indices Clustered

Los índices clustered son muy útiles para las columnas que son muy consultadas por algun rango, o accesadas en orden acendente o descendente. podemos tomar como guia los siguientes puntos sobre los índices clustered:

• Cada tabla solo puede contener un índice clustered
• El orden físico de los registros de una tabla y el orden de los registros del índice son los mismos. Por eso siempre se debe de crear un índice clustered antes que el nonclustered, si se hace de la manera inversa se regeneraria automaticamente el nonclustered.
• Para mantener unicidad en los datos se debe usar la sentencia UNIQUE al crear el índice o usando un identificador unico (unique identifier). Estos identificadores unicos son de uso interno de SQL Server y no pueden ser accesados por el usuario.
• El tamaño promedio de un indice clustered es del 5% el tamaño de la tabla, aunque este varia dependiendo de las columnas contenidas en el.
• Cuando se elimina un registro, el espacio que ocupaba es reclamado de inmediato para poder ser usado por otro registro.
• Durante la creación de un indice, SQL Server hace uso de espacio temporal en disco, por lo que requiere de 1.2 veces el tamaño de la tabla para crear el índice clustered. Este espacio ocupado es liberado automaticamente después de terminar de crear el índice.

Indices Nonclustered

Los índices nonclustered son muy utiles cuando el usuario requieren de multiples manera de accesar la información. Cuando creamos un índice nonclustered , se debe de tomar en cuenta los siguientes puntos.

• Si no se especifica el tipo de índice al crearlo, por default se crea un nonclustered.
• SQL Server automaticamente regenera los índices nonclustered cuando ocurre cualquiera de los siguientes casos.
  • Un índice clustered es eliminado
  • Un índice clustered es creado
  • Se utiliza la opción DROP_EXISTING para cambiar las columnas definidas en un índice.
• El orden de las páginas "leaf level" de un índice nonclustered difiere del orden físico de los datos de la tabla. El nivel "leaf" es ordenado de manera acendente.
• La unicidad de los registros es mantenida hasta el nivel "leaf" con llaves clustered o identificadores de registro (row identifiers).
• Se pueden tener hasta 249 índices nonclustered en una tabla.
• Los índices nonclustered funcionan mucho mejor en columnas en donde los datos son altamente selectivos.
• Se debe de crear primero el índice clustered y después los nonclustered.
• Los identificadores de registro nos dan el orden logico de los datos y consisten de un ID del archivo, número de página y ID del registro.