Hola usuario de nuestro sitio web, encontramos la respuesta a lo que buscas, deslízate y la hallarás un poco más abajo.
Solución:
Depende.
En primer lugar
¿Qué es una expresión de tabla común?
Un CTE (no recursivo) se trata de manera muy similar a otras construcciones que también se pueden usar como expresiones de tabla en línea en SQL Server. Tablas derivadas, vistas y funciones con valores de tablas en línea. Tenga en cuenta que si bien BOL dice que un CTE “puede considerarse como un conjunto de resultados temporal”, esta es una descripción puramente lógica. La mayoría de las veces no se materializa por derecho propio.
¿Qué es una tabla temporal?
Esta es una colección de filas almacenadas en páginas de datos en tempdb. Las páginas de datos pueden residir parcial o totalmente en la memoria. Además, la tabla temporal puede estar indexada y tener estadísticas de columna.
Datos de prueba
CREATE TABLE T(A INT IDENTITY PRIMARY KEY, B INT , F CHAR(8000) NULL);
INSERT INTO T(B)
SELECT TOP (1000000) 0 + CAST(NEWID() AS BINARY(4))
FROM master..spt_values v1,
master..spt_values v2;
Ejemplo 1
WITH CTE1 AS
(
SELECT A,
ABS(B) AS Abs_B,
F
FROM T
)
SELECT *
FROM CTE1
WHERE A = 780
Observe que en el plan anterior no se menciona CTE1. Simplemente accede a las tablas base directamente y se trata igual que
SELECT A,
ABS(B) AS Abs_B,
F
FROM T
WHERE A = 780
Reescribir materializando el CTE en una tabla temporal intermedia aquí sería enormemente contraproducente.
Materializando la definición del CTE de
SELECT A,
ABS(B) AS Abs_B,
F
FROM T
Implicaría copiar alrededor de 8 GB de datos en una tabla temporal, entonces todavía existe la sobrecarga de seleccionar también.
Ejemplo 2
WITH CTE2
AS (SELECT *,
ROW_NUMBER() OVER (ORDER BY A) AS RN
FROM T
WHERE B % 100000 = 0)
SELECT *
FROM CTE2 T1
CROSS APPLY (SELECT TOP (1) *
FROM CTE2 T2
WHERE T2.A > T1.A
ORDER BY T2.A) CA
El ejemplo anterior toma alrededor de 4 minutos en mi máquina.
Solo 15 filas de los 1 000 000 de valores generados aleatoriamente coinciden con el predicado, pero el costoso escaneo de la tabla se realiza 16 veces para localizarlos.
Este sería un buen candidato para materializar el resultado intermedio. La reescritura de la tabla temporal equivalente tomó 25 segundos.
INSERT INTO #T
SELECT *,
ROW_NUMBER() OVER (ORDER BY A) AS RN
FROM T
WHERE B % 100000 = 0
SELECT *
FROM #T T1
CROSS APPLY (SELECT TOP (1) *
FROM #T T2
WHERE T2.A > T1.A
ORDER BY T2.A) CA
La materialización intermedia de parte de una consulta en una tabla temporal a veces puede ser útil incluso si solo se evalúa una vez, cuando permite volver a compilar el resto de la consulta aprovechando las estadísticas sobre el resultado materializado. Un ejemplo de este enfoque se encuentra en el artículo de SQL Cat When To Break Down Complex Queries.
En algunas circunstancias, SQL Server utilizará un spool para almacenar en caché un resultado intermedio, por ejemplo, de un CTE, y evitará tener que volver a evaluar ese subárbol. Esto se analiza en el elemento Conectar (migrado) Proporcionar una sugerencia para forzar la materialización intermedia de CTE o tablas derivadas. Sin embargo, no se crean estadísticas sobre esto e incluso si el número de filas en cola fuera muy diferente del estimado, no es posible que el plan de ejecución en curso se adapte dinámicamente en respuesta (al menos en las versiones actuales). Los planes de consulta adaptativos pueden ser posibles en el futuro).
Diría que son conceptos diferentes pero no demasiado diferentes como para decir “tiza y queso”.
-
Una tabla temporal es buena para reutilizarla o para realizar varias pasadas de procesamiento en un conjunto de datos.
-
Un CTE se puede utilizar para recurrir o simplemente para mejorar la legibilidad.
Y, al igual que una vista o una función con valores de tabla en línea, también se puede tratar como una macro para ser expandido en la consulta principal -
Una tabla temporal es otra tabla con algunas reglas sobre el alcance
He almacenado procesos donde uso ambos (y también variables de tabla)
CTE tiene sus usos: cuando los datos en CTE son pequeños y hay una gran mejora en la legibilidad, como en el caso de las tablas recursivas. Sin embargo, su rendimiento ciertamente no es mejor que el de las variables de tabla y cuando se trata de tablas muy grandes, las tablas temporales superan significativamente a CTE. Esto se debe a que no puede definir índices en un CTE y cuando tiene una gran cantidad de datos que requieren unirse con otra tabla (CTE es simplemente como un macro). Si está uniendo varias tablas con millones de filas de registros en cada una, CTE funcionará significativamente peor que las tablas temporales.
Recuerda que puedes dar visibilidad a esta crónica si lograste el éxito.