Difference between CTE, Temp Table and Table Variable in MSSQL

Question

All are used to store data temporarily.

Are there any performance difference (time complexity and space complexity) for these 3 types of temporary table?

Performance issue should depend on whether the result is saved on disk or memory.

I have searched a lot but did not get satisfactory answer.

Possible duplicate of [Which are more performant, CTE or temporary tables?](http://stackoverflow.com/questions/690465/which-are-more-performant-cte-or-temporary-tables) — WillardSolutions, Jan 12 '17 at 15:01
[Difference CTE vs Temp Table](https://dba.stackexchange.com/questions/13112/whats-the-difference-between-a-cte-and-a-temp-table/13117#13117) — TT., Jan 12 '17 at 15:09
A table variable is also temporary in nature, but a different beast than a temporary table. If performance is required a temporary table will almost always outshine a table variable. [What's the difference between a temp table and table variable in SQL Server?](https://dba.stackexchange.com/a/16386/65699) — TT., Jan 12 '17 at 15:10
Keep in mind that ANY of those can and will be saved on disk when there isn't enough memory to hold the data. This is a very common misconception. That is one of the main purposes of tempdb, to hold data that has spilled over from memory. — Sean Lange, Jan 12 '17 at 15:12
Don't try to learn an "always use X for maximum performance" rule here. Use whatever fits most naturally with the rest of your code. Then, **if** performance is important, set goals, *measure* the performance, and if you're not meeting the goals, *analyze* the hot-spots and *measure* alternatives. — Damien_The_Unbeliever, Jan 12 '17 at 15:14
@TT. : The answers there are good. But some claims CTE uses memory to store its data whether tempTable uses disk. Some claims that, tempTable outperforms CTE. I am little bit confused. — Noor A Shuvo, Jan 12 '17 at 15:15
@NoorAShuvo It all depends on your scenario, your server, your resource usage at the time you run the query, the amount of data you're "crunching" or just the data you're returning, what datatypes you're using, how many calculations you are making etc. (*just to get started*). Damien_The_Unbeliever 's answer is at least the one thing you should remember from all the information here. — Radu Gheorghiu, Jan 12 '17 at 15:18
@NoorAShuvo There is no one rule that tells which is better in all cases. It all depends. Knowledge about each individually will lead you to use the right construct for the problem you are facing. In any case: a temporary table (or several) will never be a bad solution; a CTE shouldn't be used for performance reasons, and only use table variables for small or toy problems. — TT., Jan 12 '17 at 15:57

score 4 · Accepted Answer · answered Nov 17 '18 at 19:08

CTE - Common Table Expressions CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with semicolon.

With CTE1(Address, Name, Age)--Column names for CTE, which are optional
AS
(
SELECT Addr.Address, Emp.Name, Emp.Age from Address Addr
INNER JOIN EMP Emp ON Emp.EID = Addr.EID
)
SELECT * FROM CTE1 --Using CTE 
WHERE CTE1.Age > 50
ORDER BY CTE1.NAME

When to use CTE? This is used to store result of a complex sub query for further use.

This is also used to create a recursive query.

Temporary Tables In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-

Local Temp Table Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign.

CREATE TABLE #LocalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into #LocalTemp values ( 1, 'Shailendra','Noida');
GO
Select * from #LocalTemp

The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.

Global Temp Table Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.

CREATE TABLE ##GlobalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into ##GlobalTemp values ( 1, 'Shailendra','Noida');
GO
Select * from ##GlobalTemp

Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.

Table Variable This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.

 GO
 DECLARE @TProduct TABLE
 (
 SNo INT IDENTITY(1,1),
 ProductID INT,
 Qty INT
 ) 
 --Insert data to Table variable @Product 
 INSERT INTO @TProduct(ProductID,Qty)
 SELECT DISTINCT ProductID, Qty FROM ProductsSales ORDER BY ProductID ASC 
 --Select data
 Select * from @TProduct

 --Next batch
 GO
 Select * from @TProduct --gives error in next batch

Note Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.

CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.

Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory.

@coolQuazi you can checkout this link - answer by Rory https://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server — Savan Gadhiya, Sep 24 '21 at 19:07

score 1 · Answer 2 · answered Mar 29 '18 at 08:06

Fairly broad topic to cover all the ins and outs. Here are a few high level differences which would give you more ideas for researching this.

CTEs are part of the same query and should be thought of as being very similar to a sub-query. A CTE allows for better readability and code-reuse (same CTE can be reused in different parts of the overall query).

Table variables and Temporary tables should be thought of as being similar real tables but with optimizations that enable SQL server to make operations against them fast especially when used with relatively small data sets. Note that although these operate against the tempdb, that doesn't automatically mean data stored here is actually persisted to disk. With each new version of SQL server, there have been additional optimizations (memory-optimized tables for example) to make these constructs faster, especially for their mainline use case of simplifying complex queries.

See this for more information on this topic: https://www.brentozar.com/archive/2014/06/temp-tables-table-variables-memory-optimized-table-variables/

Difference between CTE, Temp Table and Table Variable in MSSQL

2 Answers2