Con el objetivo de ir montando un buen fundamento para optimizar el servidor de SQL Server, es fundamental entender los conceptos de índices ya que son uno de los pilares mas fundamentales de la optimización de consultas.

Pero ¿qué es un índice de base de datos? Según mi definición un índice es una estructura de datos que puede ayudar a mejorar las operaciones de lectura o de escritura de una tabla o vista. Esta definición difiere ligeramente de las que puede encontrar en Wikipedia, tanto es su versión de ingles como es su versión en español. Las versiones al 29 de noviembre se leen:

El índice de una base de datos es una estructura de datos que mejora la velocidad de las operaciones, por medio de identificador único de cada fila de una tabla, permitiendo un rápido acceso a los registros de una tabla en una base de datos.

Wikipedia Español

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.

Wikipedia Inglés

En la definición de español, se pueden hacer 3 criticas: el concepto de “identificador único” es innecesario y lleva a error; hace falta detallar cuales son las operaciones que se mejoran; y la más importante desde el punto de vista conceptual, las tablas tienen filas no registros. En la definición en inglés, mis críticas son que descarta la posibilidad de que los índices pueden ayudar a la escritura; que descarta los índices sobre vistas; y finalmente que sobre enfatiza el costo de mantenimiento del índice al incorporarlo en la definición.

¿Cuáles tipos de índices son soportados en SQL Server?

La siguiente tabla puede ayudarte a entender las opciones que existen en SQL Server:

Categoría	Tipo	Tipo Español	Versión SQL	Unique	Include	Filtered	Compressed
Sin Índice	Heap	Pila	–	–	–	–	–
Basado en Filas	Clustered	Agrupado	–	Disponible	–	–	Disponible
	Non-Clustered	No Agrupado	–	Disponible	Disponible	Disponible	Disponible
Basado en Columnas	ColumStore Clustered	Columnar Agrupado	+SQL 2014	–	–	–	Automático
	ColumnStore Non-Clustered	Columnar No Agrupado	+SQL 2012	–	–	Disponible	Automático
Memoria	Hash (In Memory)	Hash en Memoria	+SQL 2014	–	–	–	–
	Non-Clustered Hash	No Agrupado Hash	+SQL 2014	–	Disponible	Disponible	–
	In-Memory ColumnStore	En memoria columnar	+SQL 2016	–	–	–	–
Especiales	Spatial	Espacial	+SQL 2008	–	–	–	–
	XML	XML	+SQL 2008	–	–	–	–
	Full-Text	Texto Completo		–	–	–	–

• Las tablas en memoria y por consecuencia los índices Hash y Non-Clustered Hash tienen importantes restricciones en la versión de SQL 2014. En SQL 2016 mejoro sensiblemente su funcionalidad.
• Los columnstore indexes tiene importantes restricciones en la versión de SQL 2012, la versión de SQL 2014 mejoro sensiblemente su funcionalidad.

Revisemos la sintaxis básica de cada índice y le agregamos un brevísimo comentario sobre cada tipo que ayude a conocer el mapa general.

¿Como crear un Heap o Pila en SQL Server?

Para crear un Heap, lo importante es lo que NO haces para construirlo:

• Que la tabla no sea in-memory (optimizada en memoria).
• Que no tenga llave primaria o que si la tiene este declarada como NonClustered.
• Que no se le agregue un índice clustered.

La sintaxis seria:

CREATE TABLE dbo.CountriesHeap(
       CountryCode          CHAR(2)                    NOT NULL
        -- By Default PRIMARY KEYS Create Clustered Indexes
     CONSTRAINT PK_CountriesHeap PRIMARY KEY  NONCLUSTERED
     , CountryName        VARCHAR(50)          NOT NULL
     , CountryISO3        CHAR(3)                    NOT NULL
        -- By Default UNIQUE CONSTRAINT  Create Non-Clustered Indexes
        -- CONSTRAINT UN_CountriesHeap_CountryISO3 UNIQUE
);

Puedes bajar el código de GITHUB en IND-0010 Heap.sql.

En general los Heaps no son una buena práctica y deben evitarse excepto en casos muy específicos, como puede ser tablas para carga masiva de datos usadas en soluciones de bodegas de datos en el Stage o Andamio.

¿Como crear un índice Agrupado o Clustered Index?

Para crear un índice Clustered o agrupado debes definirlo sobre una tabla que no sea optimizada en memoria con cualquiera de las siguientes alternativas:

• Crear una llave primaria de forma predeterminada o sea sin modificarla como NonClustered; o
• Crear el índice de forma explícita.

La sintaxis para crear el índice con llave primaria es:

   CountryCode          CHAR(2)                    NOT NULL
--      By Default PRIMARY KEYS create Clustered Indexes
   CONSTRAINT PK_CountriesClust PRIMARY KEY

O su alternativa explicita:

CREATE UNIQUE CLUSTERED INDEX UN_CountriesClust
ON  dbo.CountriesClust(CountryCode)

Puedes bajar el código de IND-0011 Clustered.sql.

Es buena práctica que todas las tablas tengan un índice clustered, ya que el índice cubre casi todos los tipos de consultas (especificas, de rangos cortos, rangos largos o consultas ordenadas) pero debes considerar que una tabla solo puede tener un índice clustered y que su tamaño impacta a todos los demás índices.

¿Cómo crear un índice No Agrupado o Nonclustered Index?

Para crear un índice nonclustered o no agrupado debes: usar una tabla que no sea optimizada en memoria y tienes dos formas de crear estos índices:

• Crear una llave única de forma predeterminada o sea sin modificarla como Clustered.
• Crear el índice de forma explícita.

La sintaxis para crear el índice con UNIQUE:

   , CountryISO3        CHAR(3)                    NOT NULL
   --      By Defaulta UNIQUE CONSTRAINT creates a Non-Clustered Index
   CONSTRAINT UN_CountriesClust UNIQUE

O su alternativa explicita:

CREATE UNIQUE NONCLUSTERED INDEX UN_CountriesNoClust
ON  dbo.CountriesNoClust(CountryCode);

Puedes ver el código en IND-0012 NonClustered.sql.

Los índices nonclustered son buenos para consultas especificas (que retornan una solo fila), o consultas de rangos cortos (pocas filas), o con el modificador de INCLUDE pueden ayudar a rangos largos o consultas ordenadas. El abuso de índices nonclustered suele producir problemas de rendimiento y bloqueos en las operaciones de actualización de datos (INSERT, UPDATE y DELETE).

¿Cómo crear un índice columnar agrupado o ColumnStore Clustered?

Desde las versiones de SQL 2014 en adelante es posible crear índices ColumnStore Clustered o Columnar Agrupado. Debes definir estos índices sobre tablas que no sean en memoria. La sintaxis para crearlos es:

CREATE CLUSTERED COLUMNSTORE INDEX CC_CountriesColClust
ON dbo.CountriesColClust;

Puedes ver el código en IND-0015 ColumnClustered.sql.

Los índices ColumnStore Clustered o Columnar Agrupado son índices que están diseñados para tablas en almacenes de datos donde las tablas tienen muchos millones de filas y las consultas suelen ser agregadas.

¿Cómo crear un índice columna no agrupado o ColumnStore Non-Clustered?

Desde las versiones de SQL 2012 en adelante es posible crear índices ColumnStore Clustered o Columnar Agrupado, debes definir estos índices sobre tablas que no sean en memoria. La es:

CREATE CLUSTERED COLUMNSTORE INDEX CC_CountriesColClust
ON dbo.CountriesColClust;

Puedes ver el código en IND-0016 ColumnNoClustered.sql.

Los índices ColumnStore Clustered o Columnar Agrupado son índices que están diseñados para tablas en almacenes de datos donde las tablas tienen muchos millones de filas y las consultas suelen ser agregadas.

¿Cómo crear un índice Hash en memoria?

Para crear tablas en memoria y eventualmente crear índices Hash in-memory debes primero preparar la base de datos y agregar un grupo de archivos (FileGroup) y un “archivo” a la base de datos. Es importante señalar que las bases de datos soportan uno y solo un FileGroup que contiene datos optimizados en memoria. Este Filegroup debe tener por lo menos un “archivo” que en realidad es la ruta al folder donde se copiaran los datos en disco de las estructuras que soportan las tablas in-memory. La sintaxis modificar la estructura de la bases de datos y soportar tablas in-memory es:

ALTER DATABASE DemoIndexes 
     ADD FILEGROUP DemoIndexes_MEM CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE DemoIndexes 
    ADD FILE (name='DemoIndexes_MEM', filename='S:\MSSQL\MSSQL14.MSSQLSERVER\MSSQL\DATA\DemoIndexes_MEM') TO FILEGROUP DemoIndexes_MEM
-- Best practice
ALTER DATABASE DemoIndexes SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;

Puede ver el código en  IND-0020 InMemorySetup.sql.

Finalmente, y dependiendo de la versión de SQL Server que tengas la sintaxis para crear un índice hash in-memory y de paso su compañero Non-Clustered Hash que soporte su llave primaria es:

CREATE TABLE dbo.CountriesInMem(
     CountryCode          CHAR(2)                    NOT NULL
   -- By Default PRIMARY KEYS create Clustered Indexes
   -- In Memory tables required NONCLUSTERED PRIMARY KEY
        CONSTRAINT PK_CountriesInMem PRIMARY KEY NONCLUSTERED
   , CountryName        VARCHAR(50)          NOT NULL
        INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000)
, CountryISO3        CHAR(3)                    NOT NULL
   --  By Default UNIQUE CONSTRAINT  create Non-Clustered Indexes
   --  CONSTRAINT UN_CountriesInMem_CountryISO3 UNIQUE
) WITH (MEMORY_OPTIMIZED=ON);

Se puede notar, que la tabla está definida con la opción MEMORY_OPTIMIZED; que el índice se declara directamente cuando se construye la tabla (ya que en versiónes de SQL 2014 no se permitían cambios al esquema, pero en 2016 cambio esta limitación) y es requisito que la tabla tenga llave primaria por lo que en este caso de golpe creamos la llave primaria como non-clustered, que genera un índice No Agrupado Hash. Este último código lo puedes bajar de: IND-0021 HashAndNonClustHash.sql

Con este último índice cubrimos todos los índices de tabla y dejamos luego para otro posteo los índices especiales, como son los índices espaciales, XML y Full-Text.

Si quiere familiarizarte con la sintaxis y perder un poco el miedo a los nuevos tipos de índices que no estas acostumbrado a usar, te recomiendo que bajes el código de GitHub y lo pruebes y lo ajustes a tablas o bases con las que estés familiarizado.