Después de consultar con especialistas en la materia, programadores de varias áreas y maestros hemos dado con la solución al dilema y la dejamos plasmada en este post.
Solución:
Dejando de lado la sintaxis no era correcta (antes de la edición).
NO, no se pone en la memoria.
Un ejemplo apropiado es una combinación. Recibirá una llamada varias veces en una unión de bucle. En un CTE costoso llamado varias veces, materializarse en #temp es el camino a seguir.
En SQL, un CTE solo se puede usar/referenciar en la declaración (una), donde se define. Y sabemos dónde terminan las sentencias usando terminadores de sentencias (;).
SQL Server es indulgente y permite que los desarrolladores no coloquen estos terminadores (excepto en casos especiales en los que se queja), pero es realmente una buena práctica (y Microsoft lo recomienda) usarlos después de cada declaración. Si los hubiera colocado, sería obvio que su código se analiza como 3 declaraciones:
--- 1st statement starts ---
with ctegeneric as (select person from people where person = 'dumb')
Select * from ctegeneric ; -- and ends here
--- 2nd statement starts ---
Select * from ctegeneric ; -- and ends here
--- 3rd statement starts ---
Select * from ctegeneric ; -- and ends here
Por lo tanto, su segunda y tercera declaración no deberían funcionar en absoluto y devolverán el error:
INVALID OBJECT NAME: ctegeneric
En cuanto finaliza la declaración donde se hace el CTE, se pierde la posibilidad de volver a referenciarlo.
Es algo así como (esta tampoco es una sintaxis válida, solo otra forma de pensar en CTE):
WITH ctegeneric AS (SELECT person
FROM people
WHERE person = 'dumb')
BEGIN
Select * from ctegeneric ;
END
Sin embargo, puede ejecutar las tres selecciones con un UNION/UNION ALL:
WITH ctegeneric AS (SELECT person
FROM people
WHERE person = 'dumb')
Select * from ctegeneric
UNION
Select * from ctegeneric
UNION
Select * from ctegeneric ;
El optimizador toma el SQL enviado y lo traduce en un conjunto de acciones llamado plan de consulta. Al hacer esto, es libre de organizar esas acciones en cualquier secuencia que sea lógicamente equivalente al SQL. Esto puede dar como resultado que se acceda a un objeto mencionado en el SQL una vez, muchas veces o nunca. Esto se aplica a los objetos en el CTE.
Entonces, aunque comúnmente se observa que los objetos mencionados en el CTE se acceden solo una vez, no hay garantía de que esto suceda.
Al igual que cualquier otra declaración de SQL, los datos procesados a través de la parte CTE solo están dentro del alcance durante la duración de esa declaración. Si se hace referencia a los mismos datos en una declaración posterior, se volverá a acceder a ellos.
Al final de todo puedes encontrar las críticas de otros creadores, tú además tienes la libertad de mostrar el tuyo si te apetece.