CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2common-table-expressiontemp-tables
GreatLakes07
GreatLakes07

Reputation: 337

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

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?

Upvotes: 27

Views: 80052

Answers (2)

Sonu Rajpoot
Sonu Rajpoot

Reputation: 505

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

Upvotes: 8

bastos.sergio
bastos.sergio

Reputation: 6764

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.

Upvotes: 18

Related Questions