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

Question

What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other?

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

Answer 1

This is pretty broad, but I'll give you as general and answer as I can.

CTEs...

• Are unindexable (but can use existing indexes on referenced objects)
• Cannot have constraints
• Are essentially disposable VIEWs
• Persist only until the next query is run
• Can be recursive
• Do not have dedicated stats (rely on stats on the underlying objects)

#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 dispoable, 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.

Answer 2

CTE

Read more on MSDN

A CTE creates the table being used in memory, but is only valid for the specific query following it. When using recursion, this can be an effective structure, but bear in mind that it will need to be recreated everytime it's needed.

You might also consider here a table variable. This is used as a temp table is used, but is also in-memory only, but can be used multiple times without needing to be recreated every time. Also, if you need to persist a few records now, add a few more records after the next select, add a few more records after another op, then return just those handful of records, then this is a handy in-memory structure.

Temp Table

Read more on MSDN - Scroll down about 40% of the way

A temp table is literally a table created on disk, just in a specific database that everyone knows can be deleted. It is the responsibility of a good dev to destroy those tables when they are no longer needed, but a DBA can also wipe them.

Temporary tables come in two variety: Local and global. In terms of MS Sql Server you use a #tableName designation for local, and ##tableName designation for global (note the use of a single or double # as the identifying characteristic).

Notice that with temp tables, as opposed to table variables or CTE, you can apply indexes and the like, as these are legitimately tables in the normal sense of the word.

---

Generally I would use temp tables for longer or larger queries, and CTEs or table variables if I had a small dataset already and wanted to just quickly script up a bit of code for something small.

Answer 3

A CTE may be called repeatedly within a query and is evaluated every time it is referenced - this process can be recursive. If it is just referred once then it behaves much like a sub-query, although CTEs can be parameterised.

A temporary table is physically persisted, and may be indexed. In practice the query optimiser may also persist intermediate join or sub-query results behind the scenes, such as in spool operations, so it is not strictly true that the results of CTEs are never persisted to disk.

IIRC table variables (on the other hand) are always in-memory structures.

Answer 4

Temp table is a real object in tempdb, but cte is only a kind of wrapper around complex query to simplify syntax of organize recursion in one step.

Answer 5

Another behavior to note is that while a CTE can be referenced multiple times in other CTEs, CTEs can only be used to return data once.

Example:

--this first call will return data
select * from my_CTE

--this second call will throw an error
select * from my_CTE

Temp Tables can be used to return data as many times as needed

--this first call will return data
select * from #my_temp_table

--this second call will also return data
select * from #my_temp_table

Answer 6

@JNK; I'd say it's a bit disengenuous to say that CTEs don't improve performance. In the context of CTEs versus temp tables, I've just finished removing a swathe of junk from a suite of stored procs because some doofus must've thought there was little or no overhead to using temp tables. I shoved the lot into CTEs, except those which were legitimately going to be re-used throughout the process. I gained about 20% performance by all metrics. I then set about removing all the cursors which were trying to implement recursive processing. This was where I saw the greatest gain. I ended up slashing response times by a factor of ten.

I guess I'm agreeing with you in part, because you state that CTEs and temp tables have very different use cases. I just want to emphasise that, while not a panacea, the comprehension and correct use of CTEs can lead to some truly stellar improvements in both code quality/maintainability and speed. Since I got a handle on them, I see temp tables and cursors as the great evils of SQL processing. I can get by just fine with table variables and CTEs for almost everything now. My code is cleaner and faster.