Cómo crear índices en SQL Server con la instrucción CREATE INDEX

Resumen: en este tutorial, aprenderás a usar la instrucción CREATE INDEX de SQL Server para crear índices para tablas.

Introducción a los índices de SQL Server

Un índice es una estructura de datos que mejora la velocidad de recuperación de datos de las tablas. A diferencia de un índice agrupado, un índice ordena y almacena datos por separado de las filas de datos de la tabla. Es una copia de las columnas de datos seleccionadas de una tabla con los enlaces a la tabla asociada.

De manera similar a un índice agrupado, un índice utiliza la estructura de árbol B para organizar sus datos.

Una tabla puede tener uno o más índices y cada índice puede incluir una o más columnas de la tabla.

La siguiente imagen ilustra la estructura del índice no agrupado:

Además de almacenar los valores clave del índice, los nodos hoja también almacenan punteros de fila a las filas de datos que contienen los valores clave. Estos punteros de fila también se conocen como localizadores de fila.

Si la tabla subyacente es una tabla agrupada, el puntero de fila es la clave de índice agrupada. En caso de que la tabla subyacente sea un grupo, el puntero de fila apunta a la fila de la tabla.

Sentencia CREATE INDEX de SQL Server

Para crear un índice, usa la instrucción CREATE INDEX:

CREATE [NONCLUSTERED] INDEX index_name
ON table_name(column_list);

En esta sintaxis:

• Primero, especifica el nombre del índice después de la cláusula CREATE NONCLUSTERED INDEX. Ten en cuenta que la palabra clave NONCLUSTERED es opcional.
• En segundo lugar, especifica el nombre de la tabla en la que deseas crear el índice y una lista de columnas de esa tabla como columnas de clave de índice.

Ejemplos de sentencias CREATE INDEX de SQL Server

Usaremos sales.customers de la base de datos de muestra para los ejemplos siguientes.

La tabla sales.customers es una tabla agrupada porque tiene una clave principal customer_id.

A) Uso de la instrucción CREATE INDEX de SQL Server para crear un índice no agrupado para un ejemplo de columna

Esta declaración SELECT encuentra clientes que se ubican en Atwater:

SELECT
    customer_id,
    city
FROM
    sales.customers
WHERE
    city = 'Atwater';

Si vemos el plan de ejecución estimado, verás que el optimizador de consultas escanea el índice agrupado para encontrar la fila. Esto se debe a que la tabla sales.customers no tiene un índice para la columna de la ciudad.

Para mejorar la velocidad de esta consulta, puedes crear un nuevo índice llamado ix_customers_city para la columna de la ciudad:

CREATE INDEX ix_customers_city
ON sales.customers(city);

Ahora, si vuelves a ver el plan de ejecución estimado de la consulta anterior, encontrarás que el optimizador de consultas utiliza el índice no agrupado ix_customers_city:

B) Uso de la declaración CREATE INDEX de SQL Server para crear un índice no agrupado para el ejemplo de varias columnas

La siguiente declaración encuentra al cliente cuyo apellido es Berg y el nombre es Monika:

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name = 'Berg' AND
    first_name = 'Monika';

El optimizador de consultas escanea el índice agrupado para localizar al cliente.

Para acelerar la recuperación de datos, puedes crear un índice no agrupado que incluya las columnas apellido y nombre:

CREATE INDEX ix_customers_name
ON sales.customers(last_name, first_name);

Ahora, el optimizador de consultas usa el índice ix_customers_name para encontrar al cliente.
 

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name = 'Berg' AND
    first_name = 'Monika';

Cuando creas un índice no agrupado que consta de varias columnas, el orden de las columnas en el índice es muy importante. Debes colocar las columnas que utiliza con frecuencia para consultar datos al principio de la lista de columnas.

Por ejemplo, la siguiente declaración busca clientes cuyo apellido es Albert. Debido a que last_name es la columna más a la izquierda en el índice, el optimizador de consultas puede aprovechar el índice y usa el método de búsqueda de índice para buscar:

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name = 'Albert';

La siguiente declaración encuentra clientes cuyo nombre es Adam. También aprovecha el índice ix_customer_name. Pero necesita escanear todo el índice para buscar, lo cual es más lento que la búsqueda de índice.

SELECT
    customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    first_name = 'Adam';

Por lo tanto, es una buena práctica colocar las columnas que usas con frecuencia para consultar datos al comienzo de la lista de las columnas del índice.

En este tutorial, has aprendido sobre los índices no agrupados y cómo usar la instrucción CREATE INDEX de SQL Server para crear índices no agrupados para tablas a fin de mejorar la velocidad de recuperación de datos.