Difference Between CTE, Temp table and table variable

 Temp Table or Table variable or CTE are commonly used for storing data temporarily in SQL Server. In this article, you will learn the differences among these three.

CTE

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.

A sub query without CTE is given below :

SELECT * FROM (
 SELECT Addr.Address, Emp.Name, Emp.Age From Address Addr
 Inner join Employee Emp on Emp.EID = Addr.EID) Temp
WHERE Temp.Age > 50
ORDER BY Temp.NAME

By using CTE above query can be re-written as follows :

;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

1. This is used to store result of a complex sub query for further use.
2. 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-

1. 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.

2. 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

1. 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.
2. 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.
3. 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.
   
   -----------------------------------------------------------------------------------------------------------
   
   Both Temporary Tables (a.k.a # Tables) and Table Variables (a.k.a @ Tables) in Sql Server provide a mechanism for Temporary holding/storage of the result-set for further processing.
   Below table lists out some of the major difference between Temporary Table and Table Variable. Each of these differences are explained in-detail with extensive list of examples in the next articles in this series which are listed above.

   1. SYNTAX
   Below is the sample example of Creating a Temporary Table, Inserting records into it, retrieving the rows from it and then finally dropping the created Temporary Table. -- Create Temporary Table CREATE TABLE #Customer (Id INT, Name VARCHAR(50)) --Insert Two records INSERT INTO #Customer VALUES(1,'Basavaraj') INSERT INTO #Customer VALUES(2,'Kalpana') --Reterive the records SELECT * FROM #Customer --DROP Temporary Table DROP TABLE #Customer GO	Below is the sample example of Declaring a Table Variable, Inserting records into it and retrieving the rows from it. -- Create Table Variable DECLARE @Customer TABLE (  Id INT,  Name VARCHAR(50)   ) --Insert Two records INSERT INTO @Customer VALUES(1,'Basavaraj') INSERT INTO @Customer VALUES(2,'Kalpana') --Reterive the records SELECT * FROM @Customer GO RESULT:
   2. MODIFYING STRUCTURE
   Temporary Table structure can be changed after it’s creation it implies we can use DDL statements ALTER, CREATE, DROP. Below script creates a Temporary Table #Customer, adds Address column to it and finally the Temporary Table is dropped. --Create Temporary Table CREATE TABLE #Customer (Id INT, Name VARCHAR(50)) GO --Add Address Column ALTER TABLE #Customer ADD Address VARCHAR(400) GO --DROP Temporary Table DROP TABLE #Customer GO	Table Variables doesn’t support DDL statements like ALTER, CREATE, DROP etc, implies we can’t modify the structure of Table variable nor we can drop it explicitly.
   3. STORAGE LOCATION
   One of the most common MYTH about Temporary Table & Table Variable is that: Temporary Tables are created in TempDB and Table Variables are created In-Memory. Fact is that both are created in TempDB, below Demos prove this reality.
   4. TRANSACTIONS
   Temporary Tables honor the explicit transactions defined by the user.	Table variables doesn’t participate in the explicit transactions defined by the user.
   5. USER DEFINED FUNCTION
   Temporary Tables are not allowed in User Defined Functions.	Table Variables can be used in User Defined Functions.
   6. INDEXES
   Temporary table supports adding Indexes explicitly after Temporary Table creation and it can also have the implicit Indexes which are the result of Primary and Unique Key constraint.	Table Variables doesn’t allow the explicit addition of Indexes after it’s declaration, the only means is the implicit indexes which are created as a result of the Primary Key or Unique Key constraint defined during Table Variable declaration.
   7. SCOPE
   There are two types of Temporary Tables, one Local Temporary Tables whose name starts with single # sign and other one is Global Temporary Tables whose name starts with two # signs.Scope of the Local Temporary Table is the session in which it is created and they are dropped automatically once the session ends and we can also drop them explicitly. If a Temporary Table is created within a batch, then it can be accessed within the next batch of the same session. Whereas if a Local Temporary Table is created within a stored procedure then it can be accessed in it’s child stored procedures, but it can’t be accessed outside the stored procedure.Scope of Global Temporary Table is not only to the session which created, but they will visible to all other sessions. They can be dropped explicitly or they will get dropped automatically when the session which created it terminates and none of the other sessions are using it.	Scope of the Table variable is the Batch or Stored Procedure in which it is declared. And they can’t be dropped explicitly, they are dropped automatically when batch execution completes or the Stored Procedure execution completes.