Wednesday August 16th 2017

Categories

Insider

Archives

Difference between CTE and Temp Table

sql
From Reporting Perspective its import to to know the difference between CTE, derived tables and Temp Tables, following is the some of our attempt to elaborate the differences
A common table expression can be thought of as a temporary result that is defined within execution scope of single a single SELEC, INSERT, UPDATE, DELETE or CREATE VIEW Statement. 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, CTE can be self-referencing and can be referenced multiple times in the same query. A CTE can be used to create a recursive query. Substitute for a view when the general use of a view is not required, that you don’t have to store the definition in meta data.

Enable grouping by a column that is derived from a scaler subselect or a function that is either not deterministic or has external excess
Referencing the resulting table multiple times in the same statement.

CTE Vrs Temp Table

CTE

WITH cte (Column1, Column2, Column3)
AS
(
SELECT Column1, Column2, Column3
FROM SomeTable
)

SELECT * FROM cte
Temp Table

SELECT Column1, Column2, Column3
INTO #tmpTable
FROM SomeTable

SELECT * FROM #tmpTable

#Temp Tables

Are real materialized tables that exist in tempdb
Can be indexed
Can have constraints
Persist for the life of the current CONNECTION
Can be referenced by other queries or subprocedures
Have dedicated stats generated by the engine
As far as when to use each, they have very different use cases. If you will have a very large result set, or need to refer to it more than once, put it in a #temp table. If it needs to be recursive, is disposable, or is just to simplify something logically, a CTE is preferred.

Also, a CTE should never be used for performance. You will almost never speed things up by using a CTE, because, again, it’s just a disposable view. You can do some neat things with them but speeding up a query isn’t really one of them.