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.

Answer 2

CTE : CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is used to store the result of a complex sub-query on temporary bases. Its life is limited to the current query. It is defined by using WITH statement. It mainly used for recursive call.

Example

;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 

so in above example, I have create CTE name as myCTE , that can only be used in above query (I can not use myCTE out side of above query)

TEMP: It is also used to store the result of query on temporary bases.But Its life is limited to the current session. It is defined by using #. It does not support recursive.

Example:

select * into #tempTable from MHA 

In above query I have created temp table, now I can use it temp table out side of this query but with in session. See below

(no error)

select top (5) * from #tempTable