SQL- Difference between Temp Table and CTE

What's the difference between a temp table and Common Type Expression (CTE) in SQL Server?

In the real practice, it depends on the situation where we need to choose the current approach to complete the task by using of a CTE or a temp table. In my case, I usually use what's more convenient at that time and experiment a bit.

We can easily differentiate them based on the following contents –

Contents	Common Table Expression (CTE)	Temporary Table
Storage Location	However, a CTE always uses memory.	Stored in the tempdb.
Logical Location	They have the same semantics as updatable views. Updating or deleting from them affects the base tables as the CTE definition just gets expanded out into the query and they do not exist as objects in their own right. User-defined data types and XML collections must be in current database to use CTE.	Temporary tables always stored in the tempdb. User-defined data types and XML collections must be in tempdb to use for #temp tables.
Scope	CTE’s scope is only for the query. CTE can be referenced multiple times in other CTEs. CTEs can only be used to return data once. Mostly they are used to get recursive output. They always persist before the next query run.	Temp tables can be accessed within child batches (nested triggers, procedure, exec calls). They persist till the end of the batch session or dropped them forcefully.
Lifetime	CTEs are needed to be recreated if they needed again in the batch query.	Temp tables are created explicitly when the TSQL CREATE TABLE statement is encountered and can be dropped explicitly with DROP TABLE or will be dropped implicitly when the batch ends.
Transactions	CTEs don’t participate in transactions or locking. They are carried out as system transactions.	Temp tables operations would be carried out as part of the user transactions. A NOLOCK hint or READ UNCOMMITTED isolation level can of course be specified explicitly when working with temp tables as well.
Indexes	CTEs are not sported indexes directly.	Temporary tables comply to the same rules as permanent tables when it comes down to indexing. We need to create indexes on the temp table after fully populated the data. Indexes on temporary tables do not come at a higher priority than indexes on permanent tables
Other Functional Differences	Queries that insert into (or otherwise modify) CTEs cannot have a parallel plan. CTEs can be used inside scalar or multi-statement table UDFs. CTEs cannot have named constraints. CTEs cannot be SELECT-ed INTO, ALTER-ed, TRUNCATE-d or be the target of DBCC commands such as DBCC CHECKIDENT or of SET IDENTITY INSERT. CHECK constraints on CTEs are not considered by the optimizer for simplification, implied predicates or contradiction detection.	Queries that insert into (or otherwise modify) temp tables are not restricted in a parallel plan. Temp tables cannot be used inside a function. Temp tables have named constraints. Temp tables can be SELECT-ed INTO, ALTER-ed, TRUNCATE-d or be the target of DBCC commands such as DBCC CHECKIDENT or of SET IDENTITY INSERT. Temp tables supports table hints such as WITH (FORCESCAN). CHECK constraints on Temp tables are not considered by the optimizer for simplification, implied predicates or contradiction detection.
Statistics	No statistics is maintained on CTEs which means that any changes in data impacting CTEs will not cause recompilation of queries accessing CTEs.	Statistics is maintained on temp tables.

Please suggest your opinions. 

To know more on the different kinds of the tables in SQL Servers at

1. SQL – Wide Tables
2. SQL - Table Variables
3. SQL - Temp Table (Local & Global Temp Tables)
4. SQL - Common Table Expression
5. SQL - Difference between Table Variable and Common Type Expression
6. SQL - Difference between Temp Table and CTE
7. SQL - Difference between Temp Table and Table Variable

SQL- Difference between Temp Table and Table Variable

What's the difference between a temp table and table variable in SQL Server?

In the real practice, it depends on the situations where we need to choose the current approach to complete the task by using of a table variable or a temp table. I usually use what's more convenient at that time and experiment a bit.

We can easily differentiate them based on the following contents –

Contents	Table Variable	Temporary Table
Storage Location	Table variable is NOT necessarily memory resident. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb.	Stored in the tempdb.
Logical Location	It acts like a DDL operation whenever we create a table variable and metadata of the table variable always stored in system catalog. User-defined data types and XML collections must be in current database to use for table variables.	Temporary tables always stored in the tempdb. User-defined data types and XML collections must be in tempdb to use for #temp tables.
Scope	Table variables have a limited scope. They are accessed within the same batch and scope in which they are declared.	Temp tables can be accessed within child batches (nested triggers, procedure, exec calls).
Lifetime	Table variable are created implicitly when a batch containing a DECLARE @.. TABLE statement. They are dropped implicitly at the end of the batch execution. We cannot use the table variable before the DECLARE statement.	Temp tables are created explicitly when the TSQL CREATE TABLE statement is encountered and can be dropped explicitly with DROP TABLE or will be dropped implicitly when the batch ends.
Transactions and Locking	Table variables don’t participate in transactions or locking. They are carried out as system transactions.  This can be a useful feature, e.g. during a transaction certain activities can be logged in a table variable - if the transaction is rolled back then the table variable is available for inspection.	Temp tables operations would be carried out as part of the user transactions. A NOLOCK hint or READ UNCOMMITTED isolation level can of course be specified explicitly when working with temp tables as well.
Indexes	For versions prior to SQL Server 2014 indexes can only be created implicitly on table variables as a side effect of adding a unique constraint or primary key. Additionally table variables do not support INCLUDE columns, filtered indexes or partitioning.	Temporary tables comply to the same rules as permanent tables when it comes down to indexing. We need to create indexes on the temp table after fully populated the data. Indexes on temporary tables do not come at a higher priority than indexes on permanent tables
Other Functional Differences	Queries that insert into (or otherwise modify) table variables cannot have a parallel plan. Table variables can be used inside scalar or multi-statement table UDFs. Table variables cannot have named constraints. Table variables cannot be SELECT-ed INTO, ALTER-ed, TRUNCATE-d or be the target of DBCC commands such as DBCC CHECKIDENT or of SET IDENTITY INSERT. Table variables do not support table hints such as WITH (FORCESCAN). CHECK constraints on table variables are not considered by the optimizer for simplification, implied predicates or contradiction detection.	Queries that insert into (or otherwise modify) temp tables are not restricted in a parallel plan. Temp tables cannot be used inside a function. Temp tables have named constraints. Temp tables can be SELECT-ed INTO, ALTER-ed, TRUNCATE-d or be the target of DBCC commands such as DBCC CHECKIDENT or of SET IDENTITY INSERT. Temp tables supports table hints such as WITH (FORCESCAN). CHECK constraints on Temp tables are not considered by the optimizer for simplification, implied predicates or contradiction detection.
Statistics	No statistics is maintained on table variable which means that any changes in data impacting table variable will not cause recompilation of queries accessing table variable.	Statistics is maintained on temp tables.

Also please keep in mind, that with CTE there is a third option towards the same goal.

To know more on the different kinds of the tables in SQL Servers at

1. SQL – Wide Tables
2. SQL - Table Variables
3. SQL - Temp Table (Local & Global Temp Tables)
4. SQL - Common Table Expression
5. SQL - Difference between Table Variable and Common Type Expression
6. SQL - Difference between Temp Table and CTE
7. SQL - Difference between Temp Table and Table Variable

SQL - Temp Table (Local & Global Temp Tables)

Temporary table is also known as temp table. This is another most usable feature of the SQL Server. Temp tables are used to store temporary result set which is defined within the execution scope of a single SQL statement such as SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW. 
Temp tables have the same feature as any normal data table. We can create indexes on the temp table but it depends on the requirements.

Whenever we create a temporary table, it goes to Temporary folder of tempdb database. Based on the behavior and scope of requirement, SQL server provides two types of temp tables as given below - 

1. Local Temp Table , 
2. Global Temp Table

Local Temp Tables – They are easily accessible for single user. This is the most usable in SQL server and works only in the current connection of the user. They automatically deleted from the session whenever user disconnects from instances. We can create them by starting with hash (#) sign as given below -

1. We can create them as we create permanent data tables in the database before use them and
2. We can create them by using into temp table name from the permanent table or views in the select statements. They get the same data structure from the select data columns which is very useful to avoid data conflict.

----- syntax to create temp table create table #TempEmp ( EmpId Int, EmpName Varchar(25), EmpAge int, EmpDept varchar(6) ) ----- how to insert data into temp table Insert into #TempEmp  values (1,'ABC',21,'D001'), (2,'DEF',22,'D011'), (3,'GHI',23,'D021'), (4,'JKL',24,'D031') ----- get result from the temp table select EmpId, EmpName, EmpAge, EmpDept From #TempEmp EmpId EmpName EmpAge EmpDept 1 ABC 21 D001 2 DEF 22 D011 3 GHI 23 D021 4 JKL 24 D031 ----- get filtered result into another temp table select EmpId,EmpName, EmpAge,EmpDept into #Empbelow22 From #TempEmp where EmpAge>22 ----- pull the data from select EmpId, EmpName, EmpAge, EmpDept from #Empbelow22 EmpId EmpName EmpAge EmpDept 1 ABC 21 D001 2 DEF 22 D011 ----- drop temp tables drop table #Empbelow22, #TempEmp

Global Temp Tables –They are easily accessible for multiple users. The scope of Global temporary table is the same for the entire user for a particular connection. They automatically deleted whenever all users sessions are disconnected. We can create them by starting with double hash (##) sign. We cannot set revoke and grant permissions to the global temp table because it’s always accessible to all users.

----- syntax to create Global temp table create table ##TempEmp ( EmpId Int, EmpName Varchar(25), EmpAge int, EmpDept varchar(6) ) ----- how to insert data into Global temp table Insert into ##TempEmp  values (1,'ABC',21,'D001'), (2,'DEF',22,'D011'), (3,'GHI',23,'D021'), (4,'JKL',24,'D031') ----- get result from the Global temp table select EmpId, EmpName, EmpAge, EmpDept From ##TempEmp EmpId EmpName EmpAge EmpDept 1 ABC 21 D001 2 DEF 22 D011 3 GHI 23 D021 4 JKL 24 D031

When can we use of temp tables –

There are lots of scenarios, where we can easily use of the temp table to increase the performances and solve the problems such as –

• In the stored procedure, wherever we require a large number of row manipulations. 
• They could be the replacement of the cursor in the query to store the result set data into a temp table, then we can manipulate the data from there. 
• They are the best option to do complex join operations.
• We can use of n-numbers of temp tables to do more complex data manipulation and call them in the entire single session.

Point to remember: To avoid additional overhead and performance issues, drop the temp tables after use of the temp table because Temporary table stored on tempdb of SQL Server which is a separate database.
To know more on the different kinds of the tables in SQL Servers at

1. SQL – Wide Tables
2. SQL - Table Variables
3. SQL - Temp Table (Local & Global Temp Tables)
4. SQL - Common Table Expression
5. SQL - Difference between Table Variable and Common Type Expression
6. SQL - Difference between Temp Table and CTE
7. SQL - Difference between Temp Table and Table Variable