Cómo definir un índice agrupado en una tabla SQL Server con CREATE CLUSTERED INDEX

Resumen: en este post, aprenderás sobre el índice agrupado de SQL Server y cómo definir un índice agrupado para una tabla.

Introducción a los índices agrupados de SQL Server

La siguiente declaración crea una nueva tabla con CREATE TABLE denominada production.parts que consta de dos columnas part_id y part_name:

CREATE TABLE production.parts(
    part_id   INT NOT NULL,
    part_name VARCHAR(100)
);

Ejecuta la siguiente declaración INSERT INTO para insertar algunas filas en la tabla production.parts:

INSERT INTO
    production.parts(part_id, part_name)
VALUES
    (1,'Estrada Web Group'),
    (2,'EWG),
    (3,'Handlebar Grip'),
    (4,'Shock Absorber'),
    (5,'Fork');

La tabla production.parts no tiene una clave principal PRIMARY KEY, por lo tanto, SQL Server almacena sus filas en una estructura desordenada llamada heap o montón por su traducción al español.

Cuando consultas datos de la tabla production.parts, el optimizador de consultas necesita escanear toda la tabla para localizar la correcta.

Por ejemplo, esta declaración de SELECT consulta la parte cuyo id. De parte es 5.

SELECT
    part_id,
    part_name
FROM
    production.parts
WHERE
    part_id = 5;

Si revisas el plan de ejecución estimado en SQL Server Management Studio, puedes ver el plan de consulta que realizó el SQL Server:

Ten en cuenta que para mostrar el plan de ejecución estimado en SQL Server Management Studio, debes hacer clic en el botón Mostrar plan de ejecución estimado o seleccione la consulta y presionando el atajo de teclado Ctrl + L:

Debido a que la tabla production.parts tiene solo cinco filas, la consulta se ejecutará muy rápido. Sin embargo, si la tabla contiene una gran cantidad de filas, la búsqueda de datos llevará mucho tiempo y recursos.

Para resolver este problema, SQL Server proporciona una estructura dedicada para acelerar la recuperación de filas de una tabla llamada índice.

SQL Server tiene dos tipos de índices: índice agrupado e índice no agrupado. Nos centraremos en el índice agrupado en este tutorial, si quieres saber más del índice no agrupado puedes leer este post.

Un índice agrupado almacena filas de datos en una estructura ordenada según sus valores clave. Cada tabla tiene solo un índice agrupado porque las filas de datos solo se pueden ordenar en un orden. La tabla que tiene un índice agrupado se denomina tabla agrupada.

La siguiente imagen ilustra la estructura de un índice agrupado:

Un índice agrupado organiza los datos utilizando un árbol B (o árbol equilibrado) estructurado especial que permite búsquedas, inserciones, actualizaciones y eliminaciones en tiempo logarítmico amortizado.

En esta estructura, el nodo superior del árbol B se denomina nodo raíz. Los nodos del nivel inferior se denominan nodos hoja. Cualquier nivel de índice entre los nodos raíz y hoja se conoce como niveles intermedios.

En el B-Tree, el nodo raíz y los nodos de nivel intermedio contienen páginas de índice que contienen filas de índice. Los nodos hoja contienen las páginas de datos de la tabla subyacente. Las páginas de cada nivel del índice están vinculadas mediante otra estructura denominada lista doblemente vinculada.

Índice agrupado de SQL Server y restricción de clave principal

Cuando creas una tabla con CREATE TABLE con una clave principal PRIMARY KEY, SQL Server crea automáticamente un índice agrupado correspondiente en función de las columnas incluidas en la clave principal.

Esta declaración crea una nueva tabla denominada production.part_prices con una clave principal que incluye dos columnas: part_id y valid_from.

CREATE TABLE production.part_prices(
    part_id int,
    valid_from date,
    price decimal(18,4) not null,
    PRIMARY KEY(part_id, valid_from)
);

Si agregas una restricción de clave principal a una tabla existente que ya tiene un índice agrupado, SQL Server aplicará la clave principal mediante un índice no agrupado.

Esta declaración define una clave principal para la tabla production.parts:

ALTER TABLE production.parts
ADD PRIMARY KEY(part_id);

SQL Server creó un índice no agrupado para la clave principal.

Uso de la instrucción CREATE CLUSTERED INDEX de SQL Server para crear un índice agrupado.

En caso de que una tabla no tenga una clave primaria, lo cual es muy poco común, puede usar la instrucción CREATE CLUSTERED INDEX para definir un índice agrupado para la tabla.

La siguiente declaración crea un índice agrupado para la tabla production.parts:

CREATE CLUSTERED INDEX ix_parts_id
ON production.parts (part_id);

Si abres el nodo Índices debajo del nombre de la tabla, verás el nuevo nombre del índice ix_parts_id con el tipo Clustered.

Al ejecutar la siguiente instrucción, SQL Server utiliza el índice (búsqueda de índice agrupado) para ubicar la fila, que es más rápido que escanear toda la tabla.

SELECT
    part_id,
    part_name
FROM
    production.parts
WHERE
    part_id = 5;

Sintaxis de SQL Server CREATE CLUSTERED INDEX

La sintaxis para crear un índice agrupado es la siguiente:

CREATE CLUSTERED INDEX index_name
ON schema_name.table_name (column_list); 

En esta sintaxis:

• Primero, especifica el nombre del índice agrupado después de la cláusula CREATE CLUSTERED INDEX.
• En segundo lugar, especifica el esquema y el nombre de la tabla en la que deseas crear el índice.
• En tercer lugar, enumera una o más columnas incluidas en el índice.

En este tutorial, has aprendido sobre los índices agrupados de SQL Server y cómo usar la instrucción CREATE CLUSTERED INDEX para definir un nuevo índice agrupado para una tabla.