Which are more performant, CTE or temporary tables?

Which are more performant, `CTE` or `Temporary Tables`?

This is a really open ended question, and it all depends on how its being used and the type of temp table (Table variable or traditional table).

A traditional temp table stores the data in the temp DB, which does slow down the temp tables; however table variables do not.

Temp tables are always on disk - so as long as your CTE can be held in memory, it would most likely be faster (like a table variable, too).

But then again, if the data load of your CTE (or temp table variable) gets too big, it'll be stored on disk, too, so there's no big benefit.

In general, I prefer a CTE over a temp table since it's gone after I used it. I don't need to think about dropping it explicitly or anything.

So, no clear answer in the end, but personally, I would prefer CTE over temp tables.

CTE has its uses - when data in the CTE is small and there is strong readability improvement as with the case in recursive tables. However, its performance is certainly no better than table variables and when one is dealing with very large tables, temporary tables significantly outperform CTE. This is because you cannot define indices on a CTE and when you have large amount of data that requires joining with another table (CTE is simply like a macro). If you are joining multiple tables with millions of rows of records in each, CTE will perform significantly worse than temporary tables.

Late to the party, but...

The environment I work in is highly constrained, supporting some vendor products and providing "value-added" services like reporting. Due to policy and contract limitations, I am not usually allowed the luxury of separate table/data space and/or the ability to create permanent code [it gets a little better, depending upon the application].

IOW, I *can't* usually develop a stored procedure or UDFs or temp tables, etc. I pretty much have to do everything through MY application interface (Crystal Reports - add/link tables, set where clauses from w/in CR, etc.). One SMALL saving grace is that Crystal allows me to use COMMANDS (as well as SQL Expressions). Some things that aren't efficient through the regular add/link tables capability can be done by defining a SQL Command. I use CTEs through that and have gotten very good results "remotely". CTEs also help w/ report maintenance, not requiring that code be developed, handed to a DBA to compile, encrypt, transfer, install, and then require multiple-level testing. I can do CTEs through the local interface.

The down side of using CTEs w/ CR is, each report is separate. Each CTE must be maintained for each report. Where I can do SPs and UDFs, I can develop something that can be used by multiple reports, requiring only linking to the SP and passing parameters as if you were working on a regular table. CR is not really good at handling parameters into SQL Commands, so that aspect of the CR/CTE aspect can be lacking. In those cases, I usually try to define the CTE to return enough data (but not ALL data), and then use the record selection capabilities in CR to slice and dice that.

So... my vote is for CTEs (until I get my data space).

I'd say they are different concepts but not too different to say "chalk and cheese".

- A temp table is good for re-use or to perform multiple processing passes on a set of data.

- A CTE can be used either to recurse or to simply improved readability. <br>
And, like a view or inline table valued function can also be treated like a macro to be expanded in the main query

- A temp table is another table with some rules around scope

I have stored procs where I use both (and table variables too)

One use where I found CTE's excelled performance wise was where I needed to join a relatively complex Query on to a few tables which had a few million rows each.

I used the CTE to first select the subset based of the indexed columns to first cut these tables down to a few thousand relevant rows each and then joined the CTE to my main query. This exponentially reduced the runtime of my query.

Whilst results for the CTE are not cached and table variables might have been a better choice I really just wanted to try them out and found the fit the above scenario.

I just tested this- both CTE and non-CTE (where the query was typed out for every union instance) both took ~31 seconds. CTE made the code much more readable though- cut it down from 241 to 130 lines which is very nice. Temp table on the other hand cut it down to 132 lines, and took FIVE SECONDS to run. No joke. all of this testing was cached- the queries were all run multiple times before.

I've used both but in massive complex procedures have always found temp tables better to work with and more methodical. CTEs have their uses but generally with small data.

For example I've created sprocs that come back with results of large calculations in 15 seconds yet convert this code to run in a CTE and have seen it run in excess of 8 minutes to achieve the same results.

CTE won't take any physical space. It is just a result set we can use join.

Temp tables are temporary. We can create indexes, constrains as like normal tables for that we need to define all variables.

Temp table's scope only within the session.
EX:
Open two SQL query window

create table #temp(empid int,empname varchar)
insert into #temp
select 101,'xxx'

select * from #temp

Run this query in first window
then run the below query in second window you can find the difference.

select * from #temp