What are the Main difference between CTE's and TEMP tables?

Question

Is there a benefit to using CTE's (common table expressions) instead of using temp tables.

I went through performance testing between both of them, but I cant find much difference between them.

What are some pros and cons of using CTE'S?

Answer 1

Probably the biggest difference between a CTE and a temp table, is that the CTE has an execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

Essentially you can't reuse the CTE, like you can with temp tables.

From the documentation

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

1. Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

2. Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

3. Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

4. Reference the resulting table multiple times in the same statement.