CTE vs tabla temporal en SQL Server: cuál elegir

Comparación entre una CTE y una tabla temporal en SQL Server

Elegir entre una CTE y una tabla temporal en SQL Server depende de alcance, reutilización, indexación, cardinalidad y plan de ejecución. Una CTE da nombre a una expresión dentro de una sola sentencia; una tabla temporal almacena resultados en tempdb y puede utilizarse en varias sentencias. Ninguna opción es siempre más rápida.

Qué es una CTE

Una expresión común de tabla se declara con WITH y se referencia en la sentencia siguiente:

WITH VentasCliente AS (
    SELECT ClienteId, SUM(Importe) AS Total
    FROM dbo.Ventas
    GROUP BY ClienteId
)
SELECT c.Nombre, v.Total
FROM VentasCliente AS v
JOIN dbo.Clientes AS c ON c.ClienteId = v.ClienteId;

Mejora legibilidad y permite CTE recursivas. Su alcance termina con esa sentencia. La documentación advierte que los resultados no se materializan automáticamente; cada referencia puede implicar volver a ejecutar la consulta subyacente según el plan.

Qué es una tabla temporal

Una tabla local comienza por # y vive durante la sesión, con reglas de alcance para procedimientos:

SELECT ClienteId, SUM(Importe) AS Total
INTO #VentasCliente
FROM dbo.Ventas
GROUP BY ClienteId;

CREATE INDEX IX_VentasCliente
ON #VentasCliente (ClienteId);

Después puede usarse en varias sentencias y eliminarse explícitamente. SQL Server mantiene metadatos y estadísticas que pueden ayudar al optimizador, a cambio de escritura y gestión en tempdb.

Diferencias esenciales

Criterio	CTE	Tabla temporal
Alcance	Una sentencia	Sesión o procedimiento según contexto
Persistencia intermedia	No garantizada	Sí, en tempdb
Reutilización	Dentro de la sentencia	Varias sentencias
Índices propios	No	Sí
Estadísticas	Derivadas del plan y fuentes	Puede tener estadísticas sobre la tabla
Recursión	Soportada	Requiere lógica iterativa o carga previa

Cuándo elegir una CTE

Úsala para estructurar una consulta, aislar pasos lógicos, evitar subconsultas anidadas o expresar jerarquías. Si el resultado se consume una vez y el plan es razonable, una CTE mantiene el código compacto.

No asumas que escribir varias CTE obliga al motor a ejecutar paso por paso. El optimizador trata la sentencia de forma global y puede reordenar, combinar o repetir operaciones.

Cuándo elegir una tabla temporal

Es útil cuando reutilizas el conjunto en varias consultas, necesitas un índice específico, quieres separar fases para mejorar estimaciones o debes inspeccionar resultados intermedios. También puede reducir la complejidad de un plan enorme.

El coste incluye crear, poblar, registrar y eliminar datos en tempdb. En alta concurrencia, muchas tablas temporales grandes pueden presionar almacenamiento y metadatos. Mide el sistema completo.

Rendimiento: la respuesta está en el plan

Compara con datos y parámetros representativos. Activa el plan de ejecución real y revisa lecturas lógicas, CPU, duración, spills, estimaciones y operadores costosos. Ejecuta varias veces controlando el efecto de caché según el objetivo de la prueba.

Una CTE referenciada varias veces puede repetir trabajo, pero el optimizador también puede introducir un spool. Una tabla temporal materializa una frontera y ofrece estadísticas, aunque escribirla puede costar más que la consulta original.

Índices en la tabla temporal

Crea índices solo para accesos posteriores que lo justifiquen. Si cargas muchos datos, puede ser más barato crear el índice después de insertar; en otros casos, una clave desde el inicio ayuda. Incluye columnas y orden según joins y filtros reales.

No copies todos los índices de la tabla permanente. Cada índice aumenta escritura y espacio. Revisa el plan de las consultas que consumen #temp.

Estadísticas y recompilación

Las tablas temporales pueden disponer de estadísticas que mejoran estimaciones, especialmente cuando el número de filas intermedio es muy diferente de lo esperado. Sin embargo, cambios de cardinalidad entre ejecuciones, parámetros y recompilaciones pueden alterar el plan.

Prueba casos pequeños y grandes. Una solución ajustada a un parámetro puede degradarse con otro. Considera opciones de recompilación solo después de entender coste y frecuencia.

CTE recursiva

Las CTE recursivas son apropiadas para jerarquías y grafos simples: una parte ancla produce filas iniciales y otra parte se referencia hasta que no aparecen filas nuevas. Evita ciclos y controla profundidad con MAXRECURSION cuando proceda.

No uses recursión para cualquier transformación iterativa. Una tabla de números, una consulta de ventana o un modelo distinto puede ser más eficiente.

Tabla temporal frente a variable de tabla

Una variable de tabla no es equivalente automática ni superior por estar en memoria; también puede utilizar tempdb y sus estimaciones dependen de versión y contexto. Inclúyela como tercera opción solo si comprendes volumen, recompilación y comportamiento del optimizador.

Para resultados medianos o grandes y reutilizados, #temp suele ofrecer más opciones de índices y estadísticas. Mide siempre.

Transacciones y limpieza

Las operaciones sobre tablas temporales participan en transacciones. Un rollback puede revertir cambios. Elimina la tabla si un procedimiento largo podría crearla de nuevo en la misma sesión, o usa DROP TABLE IF EXISTS antes de una recreación controlada.

Evita nombres dinámicos y SQL concatenado sin necesidad. Parametriza consultas y aplica permisos a las tablas fuente.

Ejemplo de decisión

Si calculas ventas agregadas y las unes una vez con Clientes, empieza con CTE. Si el mismo agregado alimenta tres informes, necesita un índice por ClienteId y reduce un plan inestable, prueba una tabla temporal. Compara lecturas y tiempo bajo concurrencia.

Lista de verificación

Pregunta cuántas veces se reutiliza el resultado, cuántas filas contiene, si necesita índices, si la estimación es correcta y cuánto cuesta tempdb. Conserva la opción más sencilla que cumpla rendimiento y mantenibilidad.

La sintaxis no determina por sí sola la velocidad. CTE describe; #temp materializa. El plan, los datos y el patrón de uso deciden cuál es adecuada.

Continúa aprendiendo

Amplía este tema con CTE en SQL, índices en bases de datos, vistas SQL.

Fuentes oficiales y primarias

Preguntas frecuentes

¿Una CTE guarda sus resultados?
No se materializa automáticamente; el optimizador decide el plan y la consulta subyacente puede volver a evaluarse.

¿Dónde se almacena una tabla temporal?
En tempdb, con alcance local o global según el tipo y la sesión.

¿Cuál es más rápida?
Depende del plan, volumen, reutilización, estadísticas e índices; debe medirse con datos representativos.

¿Puedo crear índices en una CTE?
No sobre el resultado de la CTE; los índices pertenecen a sus fuentes. Una tabla temporal sí admite índices propios.

¿Cuándo conviene una CTE recursiva?
Para jerarquías o recorridos recursivos controlados, con prevención de ciclos y profundidad adecuada.

¿Una variable de tabla vive solo en memoria?
No necesariamente; puede usar tempdb y su comportamiento depende de versión, volumen y contexto.

Deja una respuesta Cancelar la respuesta