SQL Server - CREATE INDEX
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.
En conclusión, los índices son una herramienta fundamental en SQL Server para mejorar el
rendimiento de las consultas y reducir el tiempo de búsqueda de datos en grandes bases de datos.
Es importante comprender los diferentes tipos de índices disponibles en SQL Server y cómo
utilizarlos de manera efectiva en función de las necesidades específicas de su aplicación. Se debe
tener en cuenta que la selección de un tipo de índice incorrecto o la aplicación inadecuada de un
índice puede tener un impacto negativo en el rendimiento de la base de datos. Por lo tanto, se
debe considerar cuidadosamente cada caso y realizar pruebas exhaustivas antes de implementar
cualquier tipo de índice.
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.
1
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.
TIPOS DE ÍNDICES EN SQL SERVER
Resumen: Con este artículo, aprenderás qué son los índices en SQL Server, los diferentes tipos de
índices que existen y cuándo es apropiado utilizar cada uno de ellos. También puedes entender las
ventajas y desventajas de cada tipo de índice y cómo se pueden utilizar en la optimización de
consultas y mejora del rendimiento en la base de datos.
Introducción a los índices
En SQL Server, los índices son estructuras de datos que se utilizan para mejorar la eficiencia de
las consultas a una tabla. Estos índices contienen copias selectivas de los datos de la tabla,
organizados de una manera que permite que las consultas encuentren los datos más rápido.
Los índices son especialmente importantes en las bases de datos relacionales grandes, donde las
consultas pueden ser muy complejas y los tiempos de respuesta de la consulta pueden ser críticos.
Al agregar índices cuidadosamente diseñados a las tablas, se puede acelerar significativamente el
proceso de recuperación de datos y mejorar el rendimiento general de la base de datos.
En este artículo, exploraremos los diferentes tipos de índices disponibles en SQL Server y
discutiremos cuándo es mejor usar cada uno de ellos para obtener el máximo beneficio de tu base
de datos.
Tipos de índices
A continuación, te presento los tipos de índices que existen en SQL Server:
Clustered Index: Es un tipo de índice que determina el orden físico de los datos en una
tabla. Solo puede haber un índice agrupado por tabla y este ordena la tabla en función de
la clave primaria. Es decir, los datos se almacenan en el disco en función de los valores de
la columna de la clave primaria.
Nonclustered Index: A diferencia del índice agrupado, los índices no agrupados no ordenan
físicamente la tabla. En su lugar, crean una estructura separada que incluye una copia de
la columna de la clave primaria y la columna de índice. Esto permite una búsqueda más
rápida de datos en la tabla.
Unique Index: Este tipo de índice se utiliza para garantizar que no se inserten valores
duplicados en una tabla. Es similar a un índice no agrupado, pero solo puede haber un
valor único para cada valor de la clave.
Filtered Index: Este tipo de índice se utiliza para filtrar datos específicos en una tabla. Solo
incluyen filas que cumplen con una condición específica. Esto reduce el tamaño del índice
y mejora la velocidad de búsqueda de datos.
Spatial Index: Este tipo de índice se utiliza para buscar datos basados en su ubicación en
un espacio tridimensional. Los datos espaciales incluyen información como la latitud,
longitud y altitud.
Full-Text Index: Este tipo de índice se utiliza para buscar texto completo en una tabla.
Permite la búsqueda de palabras clave y frases en lugar de simplemente buscar
coincidencias exactas.
2
Es importante conocer los diferentes tipos de índices para poder elegir el más adecuado según las
necesidades de la base de datos y mejorar la eficiencia de las consultas.
EJEMPLOS DE USO
Para cada tipo de índice, se proporcionan ejemplos concretos de cómo se utilizan en SQL Server.
Clustered Index
Un clustered index define el orden físico de los datos en la tabla, y una tabla sólo puede tener uno.
Por ejemplo:
CREATE CLUSTERED INDEX idx_orders_orderdate ON orders (orderdate);
Este ejemplo crea un clustered index en la columna orderdate de la tabla orders, lo que significa
que los registros se ordenarán físicamente en la tabla según la fecha del pedido.
Nonclustered Index
Un nonclustered index es una estructura de datos separada de la tabla que contiene una copia de
parte de la información de la tabla organizada para una rápida búsqueda. Por ejemplo:
CREATE NONCLUSTERED INDEX idx_customers_lastname ON customers (lastname);
Este ejemplo crea un nonclustered index en la columna lastname de la tabla customers, lo que
significa que se puede realizar una búsqueda rápida de registros según el apellido del cliente.
Unique Index
Un unique index asegura que no puede haber valores duplicados en una columna determinada.
Por ejemplo:
CREATE UNIQUE INDEX idx_products_productcode ON products (productcode);
Este ejemplo crea un unique index en la columna productcode de la tabla products, lo que significa
que no puede haber dos productos con el mismo código.
Filtered Index
Un filtered index es un index que se limita a un subconjunto de filas de una tabla. Por ejemplo:
CREATE INDEX idx_orders_recent ON orders (orderdate) WHERE orderdate >= '2022-01-01';
Este ejemplo crea un filtered index en la columna orderdate de la tabla orders, pero sólo para los
pedidos realizados después del 1 de enero de 2022.
Spatial Index
3
Un spatial index es un tipo de index que se utiliza para optimizar la búsqueda de datos espaciales,
es decir, datos que tienen una ubicación geográfica o física. Por ejemplo:
CREATE SPATIAL INDEX idx_locations_coords ON locations (coords);
Este ejemplo crea un spatial index en la columna coords de la tabla locations, lo que significa que
se puede realizar una búsqueda rápida de registros según la ubicación geográfica.
Full-Text Index
Un full-text index se utiliza para mejorar la velocidad de las búsquedas de texto en grandes
cantidades de datos de texto. Por ejemplo:
CREATE FULLTEXT INDEX idx_products_description ON products (description) KEY INDEX
Este ejemplo crea un full-text index en la columna description de la tabla products, lo que significa
que se puede realizar una búsqueda rápida de productos según la descripción. El index utiliza la
columna productid como clave.
VENTAJAS Y DESVENTAJAS DE CADA TIPO DE ÍNDICE
A continuación, te proporciono a mi parecer cuales son las ventajas y desventajas de cada tipo de
índice:
1. Clustered Index:
Ventajas:
El ordenamiento de las filas en la tabla se realiza físicamente en el índice, lo que mejora el
rendimiento en la búsqueda de datos.
Las consultas que devuelven un rango de valores se ejecutan más rápido.
Desventajas:
Solo se permite un índice clusterizado por tabla.
Las actualizaciones frecuentes en la tabla pueden causar fragmentación del índice.
2. Nonclustered Index:
Ventajas:
Permiten tener varios índices por tabla.
Mejoran el rendimiento de las consultas SELECT que utilizan los campos que están
incluidos en el índice.
Desventajas:
Requieren más espacio en disco que los índices clusterizados.
Las actualizaciones frecuentes en la tabla pueden causar fragmentación del índice.
3. Unique Index:
Ventajas:
4
Garantiza que los valores de la columna indexada sean únicos.
Acelera la búsqueda de valores únicos en la tabla.
Desventajas:
Las actualizaciones frecuentes en la tabla pueden causar fragmentación del índice.
El costo de la verificación de unicidad puede ser alto en tablas grandes.
4. Filtered Index:
Ventajas:
Se pueden crear en una columna que contiene un subconjunto de los valores de la tabla, lo
que reduce el tamaño del índice y mejora la velocidad de búsqueda.
Desventajas:
No se pueden usar para consultas que buscan valores fuera del subconjunto definido en el
índice.
Las actualizaciones frecuentes en la tabla pueden causar fragmentación del índice.
5. Spatial Index:
Ventajas:
Optimiza la búsqueda de datos geográficos.
Permite realizar operaciones de búsqueda basadas en la proximidad geográfica.
Desventajas:
Requiere espacio adicional en disco para almacenar los datos de los índices.
Puede requerir un procesamiento significativo para crear y mantener los índices.
6. Full-Text Index:
Ventajas:
Optimiza la búsqueda de texto libre en grandes cantidades de datos.
Permite realizar búsquedas de texto en diferentes idiomas.
Desventajas:
Requiere espacio adicional en disco para almacenar los datos de los índices.
El costo del procesamiento para crear y mantener los índices puede ser alto.
Es importante recordar que la elección del tipo de índice dependerá de las necesidades específicas
de la aplicación y de la tabla en cuestión.
CREATE INDEX de SQL Server
5
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 [Link] de la base de datos de muestra para los ejemplos siguientes.
La tabla [Link] 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
[Link]
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 [Link] no tiene un índice
para la columna de la ciudad.
6
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 [Link](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
[Link]
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 [Link](last_name, first_name);
7
Ahora, el optimizador de consultas usa el índice ix_customers_name para encontrar al cliente.
SELECT
customer_id,
first_name,
last_name
FROM
[Link]
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
[Link]
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
8
FROM
[Link]
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.
