Temporary Tables vs. Common Table Expression (CTE)

Today I want to show you how to use CTE and explain the difference
between CTE and a temporary tables.

Temporary Table
local temporary table
Local temporary tables (starting with a single #) are created in the tempdb and are only available in the current session. After closing the session the are dropped automatically.
global temporary table
Global temporary table (staring with two #) are created in the tempdb and are available in all sessions. They are automatically dropped after all user connections are closed.

CTE
CTE is not a table it is a temporary result set with scope on the current query, so it is not limited by the session it is limited by the current select.

Difference to a temporary table
CTE is a temporary result created in memory not in tempdb.
CTE cannot have an index.
CTE is only for the current statement.
CTE is faster (no table Creation)



Lets have a look at the execution plans
CTE (Subtreecost 0,313466)

local temp (Subtreecost 11,7748 for creating temptable + 0,313466 for select from temptable = 12,088266)



global temp (Subtreecost 11,7748 for creating temptable + 0,313466 for select from temptable = 12,088266)



As you can see CTE is not as expensive as temp tables but it can't be indexed and is pointed to current statement.
You should use CTE for inline subquerys or aggregating. For complex procedures or scripts you have to use temptables.

Copyable Version:
-- CTE
;With CTEResult(Itemno, itemname, Price)
AS
(
SELECT Itemno, itemname, Price from article a
)
SELECT * FROM CTEResult cr
WHERE cr.Itemno in (1,2,50)
ORDER BY cr.itemname
-- localtemptable
SELECT Itemno, itemname, Price into #localtemptable from article a
select * from #localtemptable where itemno in (1,2,50) order by Itemname
-- globaltemptable
SELECT Itemno, itemname, Price into ##globaltemptable from article a
select * from ##globaltemptable where itemno in (1,2,50) order by Itemname


drop table #localtemptable
drop table ##globaltemptable