How long does a CTE live?

Question

Accepted Answer

A CTE's lifetime is exactly one statement. Once the query it belongs to finishes executing, the CTE is gone. If you need to reuse the result across multiple queries or statements, use a temporary table (CREATE TEMP TABLE) or a view (CREATE VIEW) instead. This scoping rule is what distinguishes CTEs from views and temp tables.