Índices en SQL Server 2ª Parte

Creación de índices

Aquí la primera parte:

ÍNDICES EN SQL Server

Existen tres métodos para definir índices:

Explícitamente mediante el comando CREATE INDEX

Implícitamente en el proceso de creación de la tabla.

A través del administrador (Management Studio).

En la entrada  anterior se describen con detalle los índices en SQL Server.

Con el comando CREATE INDEX

Simplemente deberemos ejecutar el comando CREATE INDEX, cuya sintaxis simplificada es la siguiente:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX nombre_indice

ON <object> ( columna [ ASC | DESC ] [ ,...n ] )

Ejemplo:

CREATE TABLE model.tbTabla4 (Campo1 int, Campo2 int, Campo3 AS Campo1 + Campo2);

CREATE UNIQUE CLUSTERED INDEX Idx1 ON tbTabla4(Campo3);

INSERT INTO tbTabla4 VALUES (1, 0);

Por defecto, el índice es creado como unclustered, salvo que se  indique el parámetro clustered explícitamente.

La lista de columnas indica las claves por las que se ordenarán los elementos en el árbol balanceado del índice.

Índices con claves únicas. El parámetro unique permite especificar que el índice será único, es decir, que no se aceptará que dos registros de la tabla tengan la misma combinación de columnas de índice.

Los parámetros pad_index y fillfactor permiten especificar el espacio interior y externo del índice, respectivamente, que se dejará vacío en las páginas de índice, para que ese espacio pueda ser ocupado en las modificaciones que deban aplicarse al índice al ir alterando los datos de la tabla.

Creando índices en la definición de la tabla
Cada vez que creamos una tabla se crea un índice si se usan las siguientes cláusulas en la sentencia CREATE TABLE:

PRIMARY KEY: Crea una clave primaria, acción que provoca la creación de un índice de valores únicos sobre las columnas clave.

UNIQUE: Define una columna con valores únicos y a su vez crea un índice del mismo tipo.

CLUSTERED: Crea un índice clustered.

NON CLUSTERED: Crea un índice unclustered.

Creación de índices con el Explorador de Objetos del Administrador Corporativo (Management Studio)

El Administrador Corporativo permite crear índices simplemente pulsando sobre una tabla de una base de datos el botón derecho del ratón con lo que sale  el menú correspondiente del cual seleccionamos la opción Diseño.

Esto nos abre la pantalla de diseño de tablas, sobre esta pulsando de nuevo el botón derecho del ratón nos muestra otro submenú del cual elegimos la opción índices o claves.

Esto nos abre una nueva pantalla en la que podemos pulsar el botón agregar para añadir un índice.

Sobre las propiedades de la derecha podemos elegir si el índice será clustered, unclustered,  unique o no. Entre otras características que podemos añadir.

Pulsando el botón … de la derecha de la propiedad columnas, nos permite definir las columnas del índice.

Actualización de estadísticas

Cuando se crea un índice, SQL Server genera un conjunto de estadísticas sobre los datos asociados. Estas estadísticas son utilizadas por el optimizador de consultas para determinar si se deben usar los índices para ejecutar una consulta determinada.

Para mantener las estadísticas actualizadas es necesario llevar a cabo una tarea que puede realizarse mediante el comando UPDATE STATISTICS. este comando actualiza periódicamente las estadísticas. Conveniente ejecutar este comando periódicamente. El comando tiene la siguiente sintaxis: UPDATE STATISTICS.

UPDATE STATISTICS table_or_indexed_view_name

    [

        {

            { index_or_statistics__name }

          | ( { index_or_statistics_name } [ ,...n ] )  

                } 

    ]

Ejemplo.

USE model;

GO

UPDATE STATISTICS dbo.tbTabla2;

GO

También se puede utilizar el procedimiento almacenado del sistema updatestats.

EXEC sp_updatestats;

Índices a actualizar

Debe especificarse el nombre del índice al que la operación de actualización va a afectar, así como la tabla a la que pertenece. Si no se especifica un nombre de índice, se actualizarán las estadísticas para todos los índices de la tabla.

Volumen de registros a considerar

Para obtener estadísticas se pueden considerar todos los registros de la tabla o solamente una parte de ellos. El primer caso se realiza especificando  la cláusula FULLSCAN. En el segundo se escribe SAMPLE porcentaje PERCENT O SAMPLE número de registros ROWS, según deseemos que se tenga en cuenta  un número relativo o absoluto de registros a considerar, respectivamente. De todas formas, si SQL Server considera insuficiente el volumen de registros que se especifican, calculará el valor adecuado y lo utilizará.

 Actualización de estadísticas obsoletas

Por defecto, SQL Server recalculará las estadísticas obsoletas cuando se produce un volumen de modificaciones de los datos suficiente para que esto ocurra. Si especificamos la cláusula NORECOMPUTE, esta actualización no se llevará a cabo.

Elegir entre índices Clustered y Unclustered. Consideraciones de rendimiento

Antes de crear índices es importante hacer un pequeño estudio sobre cómo serán las consultas a realizar para determinar si creamos un índice de tipo clustered o de tipo unclustered.

Para elegir índices Clustered

A la hora de crear un índice de tipo clustered es de gran utilidad que las consultas reúnan estas condiciones:

- Consultas que seleccionen un rango de valores o se necesite los resultados ordenados. Es decir consultas que contengan BETWEEN, <,>, GROUP BY, ORDER BY, JOIN, MAX, MIN, COUNT.

- Consultas que busquen un registro con clave única (un nº de empleado por ejemplo) y de necesite devolver la mayoría de los datos del registro.

-Consultas que acceden a columnas con un número limitado  de distintos valores, como columnas que almacenan datos de provincias o países. Pero si la columna contiene valores poco descriptivos como Si, No, Hombre o Mujer entonces esas columnas no deben ser indexadas.

- Consultas que retornen muchas filas.

A la hora de crear un índice debe evitarse seleccionar columnas que se actualizan frecuentemente. Pero si hay que seleccionar columnas que contengan campos que se devuelvan en la mayoría de las búsquedas de las consultas.

Si una tabla contiene índices de ambos tipos clustered y unclustered, se mejora el rendimiento si el índice clustered está basado en una única columna.

La clave primaria de la tabla no siempre deberá ser un índice cluster. Al crearse la clave primaria y no especificar nada más por defecto se crea un índice tipo cluster. Se recomienda crear un índice cluster sobre la Primary Key si regularmente se extraen consultas por rangos sobre esa clave o si los resultados se ordenan por la clave primaria.

Para elegir índices Unclustered

Se recomienda crear índices unclustered para:

- Consultas que devuelvan pocos registros y donde la selectividad del índice ronde cerca del 95%.

-Consultas que devuelvan pequeños rangos de datos.

- Consultas donde están especificadas las cláusulas WHERE y ORDER BY para la misma columna de la consulta.

- Cuando las columnas indexadas sean muy grandes.