Elegir el índice correcto puede transformar el rendimiento de una base de datos, pero un índice mal colocado genera más problemas de los que resuelve. Comprender cómo funcionan los índices clustered, nonclustered, filtrados y las técnicas de paginación es fundamental para escribir consultas eficientes en SQL Server.
¿Qué es un índice y por qué puede ser contraproducente?
Un índice funciona exactamente como el índice de un libro: acelera la búsqueda pero hace más lenta la escritura [0:03]. Cada vez que se inserta o modifica un registro, SQL Server debe actualizar también el índice. Por eso, la decisión de dónde colocar índices no debe basarse en la intuición, sino en el análisis de las queries más frecuentes que se ejecutan contra la base de datos.
Para consultar qué índices existen en una tabla se puede utilizar el procedimiento almacenado sp_helpindex seguido del nombre de la tabla [1:13]. El resultado muestra el tipo de cada índice y las columnas sobre las que opera.
¿Cuál es la diferencia entre un índice clustered y uno nonclustered?
El índice clustered define el orden físico de las filas dentro de la tabla [0:41]. Una tabla solo puede tener uno, y por defecto SQL Server lo crea sobre la primary key. Si una tabla no lo tiene, se puede crear con la sentencia:
sql CREATE CLUSTERED INDEX IX_Pedidos_PedidoID ON Pedidos (PedidoID);
El índice nonclustered es una estructura separada que contiene los valores de las columnas indexadas y un puntero al registro en el índice clustered [2:13]. Siguiendo la analogía del libro, si el clustered es el índice general página por página, el nonclustered es un índice especializado que lista solo las páginas donde aparece un personaje o tema en particular.
Para crearlo se usa una sintaxis similar con el prefijo IX como convención de nomenclatura [3:18]:
sql CREATE NONCLUSTERED INDEX IX_Pedidos_FechaPedido ON Pedidos (FechaPedido);
Este índice permite que las consultas por rango de fechas se ejecuten mucho más rápido al recorrer directamente la estructura indexada [4:08].
¿Cómo eliminar el key lookup con INCLUDE?
El key lookup es la operación que SQL Server realiza cuando necesita columnas que no están en el índice y debe volver a la tabla base para obtenerlas. El comando INCLUDE agrega columnas al índice sin que formen parte de la llave de búsqueda, eliminando ese paso adicional [4:28]:
sql CREATE NONCLUSTERED INDEX IX_Pedidos_Fecha_Estado ON Pedidos (FechaPedido, Estado) INCLUDE (Total, ClienteID);
Al incluir Total y ClienteID, la query obtiene toda la información directamente del índice sin realizar búsquedas adicionales.
¿Qué son los índices filtrados?
Un índice filtrado indexa solo las filas que cumplen una condición específica [5:17]. Son más pequeños, más rápidos de mantener y funcionan cuando el optimizador detecta que la query tiene el mismo filtro:
sql CREATE NONCLUSTERED INDEX IX_Pedidos_Completados ON Pedidos (FechaPedido) WHERE Estado = 'Completado';
Este índice solo opera sobre los pedidos completados, reduciendo significativamente su tamaño.
¿Cómo paginar resultados con OFFSET, FETCH y keyset pagination?
Cuando las consultas devuelven miles de filas, la paginación se vuelve imprescindible. OFFSET y FETCH permiten dividir resultados en páginas de tamaño controlado [6:08]. El ORDER BY es obligatorio para que la paginación funcione correctamente:
sql DECLARE @Pagina INT = 1, @TamanoPagina INT = 20;
SELECT PedidoID, FechaPedido, Total FROM Pedidos ORDER BY FechaPedido OFFSET (@Pagina - 1) * @TamanoPagina ROWS FETCH NEXT @TamanoPagina ROWS ONLY;
Cambiando el valor de @Pagina se obtienen las siguientes veinte filas [7:17]. Para conocer el total de páginas disponibles se utiliza CEILING con COUNT [8:08]:
sql SELECT CEILING(COUNT(*) * 1.0 / @TamanoPagina) AS TotalPaginas FROM Pedidos;
Sin embargo, OFFSET tiene un problema de escalabilidad en tablas grandes [8:55]. Para llegar a la página cinco mil, SQL Server debe saltar miles de filas previamente. La solución es la keyset pagination o paginación por cursor, que en lugar de saltar filas utiliza el último valor visto como punto de partida [9:20]:
sql SELECT TOP 20 PedidoID, FechaPedido, Total FROM Pedidos WHERE Estado = 'Completado' AND (FechaPedido < @UltimaFecha OR (FechaPedido = @UltimaFecha AND PedidoID < @UltimoPedidoID)) ORDER BY FechaPedido DESC, PedidoID DESC;
Esta técnica consulta solo el rango necesario sin recorrer toda la tabla, lo que la convierte en la opción ideal para volúmenes muy grandes.
La regla de oro es clara: crea índices basados en queries reales, no en suposiciones [10:53]. Analiza las consultas más frecuentes, genera los índices adecuados y compara los planes de ejecución antes y después. ¿Ya probaste paginar tus resultados con OFFSET y FETCH? Comparte tus planes de ejecución y tiempos en los comentarios.
