maxrecursion - sql server table with

¿Cuáles son las principales diferencias entre las tablas CTE y TEMP?

Question

¿Existe algún beneficio al usar CTE''s ( common table expressions ) en lugar de usar temp tables ?

Pasé por pruebas de rendimiento entre ambos, pero no puedo encontrar mucha diferencia entre ellos.

¿Cuáles son algunas ventajas y desventajas de usar CTE''S ?

Answer 1

Probablemente la mayor diferencia entre un CTE y una tabla temporal, es que el CTE tiene un alcance de ejecución de una sola instrucción SELECT, INSERT, UPDATE, DELETE o CREATE VIEW.

Esencialmente, no puede reutilizar el CTE, como puede hacerlo con las tablas temporales.

De la documentation

Una expresión de tabla común (CTE) se puede considerar como un conjunto de resultados temporal que se define dentro del alcance de ejecución de una sola instrucción SELECT, INSERT, UPDATE, DELETE o CREATE VIEW. Un CTE es similar a una tabla derivada en que no se almacena como un objeto y dura solo la duración de la consulta. A diferencia de una tabla derivada, un CTE puede ser auto-referenciado y puede ser referenciado varias veces en la misma consulta.

Un CTE se puede utilizar para:

1. Crear una consulta recursiva. Para obtener más información, consulte Consultas recursivas mediante expresiones de tabla comunes.

2. Sustituya una vista cuando no se requiera el uso general de una vista; es decir, no es necesario almacenar la definición en metadatos.

3. Habilite la agrupación por una columna que se deriva de una subselección escalar, o una función que no es determinista o tiene acceso externo.

4. Haga referencia a la tabla resultante varias veces en la misma declaración.

Answer 2

CTE: CTE significa expresiones de tabla comunes. Se introdujo con SQL Server 2005. Se utiliza para almacenar el resultado de una subconsulta compleja en bases temporales. Su vida se limita a la consulta actual. Se define utilizando la sentencia WITH. Se utiliza principalmente para la llamada recursiva.

Ejemplo

;with myCTE as 
(
    select ParentLevel, ParentID, ChildID 
    from MHA 
    where ChildID = 1 
    UNION ALL
    select MHA.ParentLevel, MHA.ParentID, MHA.ChildID 
    from MHA
    inner join myCTE on MHA.ParentID = myCTE.ChildID
    where MHA.ParentID <> 0
)

(error)

select top (5) * from myCTE

así que en el ejemplo anterior, he creado el nombre CTE como myCTE, que solo se puede usar en la consulta anterior (no puedo usar myCTE al margen de la consulta anterior)

TEMP: También se usa para almacenar el resultado de la consulta en bases temporales. Pero su vida está limitada a la sesión actual. Se define utilizando #. No soporta recursivo.

Ejemplo:

select * into #tempTable from MHA

En la consulta anterior, he creado la tabla temporal, ahora puedo usarla fuera de esta consulta pero en sesión. Vea abajo

(No hay error)

select top (5) * from #tempTable