Total Page Preview:   000000021545

Difference between CTE(Common Table Expressions) and Table Variable and Temp Table in SQL SERVER ?

Temp 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-


Different Types of Temporary Tables

SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:

  1. Local Temp Table
  2. Global Temp Table


01.Local Temp Table:

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.


Example :



 UserID int,
 Name varchar(50), 
 Address varchar(150)
insert into #LocalTemp  values ( 1, 'Brijesh Kumar','Noida');
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.
01.Global Temp Table:

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.


Example :



 UserID int,
 Name varchar(50), 
 Address varchar(150)
insert into ##GlobalTemp  values ( 1, 'Brijesh Kumar','Noida');
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 Variables :

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.



EmpName VARCHAR(50),
--Insert data to Table variable @TEmployee
INSERT INTO @TEmployee(EmpName , Age)
--Select data
Select * from @TEmployee
--Next batch
Select * from @TEmployee --gives error in next batch



CTE (Common Table Expressions)  :


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.

Example :

;WITH Emp([Eid], [Salary]) --Column names for CTE, which are optional
    SELECT TOP 3 [Eid], [Salary] FROM [TestDB].[dbo].[Employee] ORDER BY Salary DESC

 --User CTE
  SELECT TOP 1 [Eid], [Salary] FROM emp ORDER BY Salary ASC


Difference between CTE(Common Table Expressions) and Table Variable :

  CTE Table Variable
1 CTE is like Derived Table or even like a sub-query. So it doesn't store on disk. Table variable is like Temporary Table. It stores on disk.
2 You cannot create any index on CTE You can create clustered index but can not create Non clustered index
3 Scope of CTE  is within the session Scope of table variable is within the batch
Difference Between CTE And Table Variable


Difference between CTE(Common Table Expressions) and #Temp Table :

  CTE #Temp Table
1 Are unindexable (but can use existing indexes on referenced objects). Are real materialized tables that exist in tempdb
2 Cannot have constraints Can be indexed
3 Are essentially disposable VIEWs Can have constraints
4 Persist only until the next query is run Persist for the life of the current CONNECTION
5 Can be recursive Can be referenced by other queries or subprocedures
6 Do not have dedicated stats (rely on stats on the underlying objects) Have dedicated stats generated by the engine
Defference CTE And Temp Table in SQL Server



Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn :


Brijesh Kumar
This is very good and helpful article.
Very helpful article




Facebook User: