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?

Solution 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.

Solution 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

Comments

[Martin Smith]

From the DBA site What's the difference between a CTE and a Temp Table?

[Hart CO]

I don't know much about the performance differences, but cte's allow for simple looping and recursion, temp tables are handy if a query output will be accessed multiple times and re-running would be a waste of time.

[GarethD]

They are different things, and really it is horses for courses. Which one is best will depend on your exact scenario. Your question is no less ambiguous than What are the benefits of using Views rather than tables?

[vhadalgi]

CTE's also perform slower because the results are not cached. So everytime you use the CTE it re-runs the query, plan and all

[spinjector]

^ That's an important point, especially if the CTE/TT is working with millions of rows of data.

[Martin Smith]

The biggest difference to me is that one is an actual table and the other isn't. There is a big difference between WITH CTE AS (SELECT * FROM MillionRowTable) SELECT TOP 1 * FROM CTE and INSERT INTO #T SELECT * FROM MillionRowTable; SELECT TOP 1 * FROM #T

[ebram khalil]

@MartinSmith your comment has more votes than the answer itself!!

[Roman Starkov]

It can go the other way too: insert into #table followed by select .. where xyz not in #table is fast, but rewriting as a CTE makes it super slow.