Difference between Temporary Tables, Table Variable and CTE

In this article we will learn about Difference between Temporary Tables, Table Variable and CTE. In SQL Server, we have various options for storing data temporarily. Temp Table, Table variable and CTE are commonly used way for storing temporary data. Please read my previous article SQL Injection and Bulk Import in SQL Server.

Difference between Temporary Tables, Table Variable and CTE

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 the Tempdb database.

The name “temporary” is slightly misleading, for even though the tables are instantiated in tempdb, they are backed by physical disk and are even logged into the transaction log.  They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements

Temporary tables are of two types :

1. Local Temporary Table
2. Global Temporary Table

Local Temporary Table

Local temp tables are only available to the SQL Server session that created the tables. These are automatically deleted when the session that created the tables has been closed. The local temporary table name is stared with a single hash (“#”) sign.

CREATE TABLE #SampleLocalTemp  
 (  
  ID int,  
  Name varchar(50),   
  Address varchar(150)  
 )  
 GO  
 insert into #SampleLocalTemp values ( 1, 'Jayant','USA');  
 GO  
 insert into #SampleLocalTemp values ( 1, 'Jhon','UK');  
 GO  
 insert into #SampleLocalTemp values ( 1, 'ALI','UAE');  
 GO  
 Select * from #SampleLocalTemp

When we run the script the output should like this. You can see that the inserted data are show and the temp table are stored in System Databases-> Tempdb-> Temporary Tables.

The scope of Local temp table exists to the current session of the 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.

It can be clearly visible that I am using the same database as “TestDB” and if I am opening a new instance in SQL server then to access the temporary table it show Invalid object name, it means the Temporary tables are accessible within Current Query window/Scope.

Global Temporary Table

Global temporary tables are temporary tables that are available to all sessions and all users. Once this table has been created by a connection, like a permanent table, then it is available to any user by any connection. They are dropped automatically when the last session using the temporary table has completed. Names of Global temp tables start with (“##”) double hash sign.

Create Table ##SampleGlobalTemp  
    (  
        ID int identity(1, 1),  
        Name varchar(50),  
        Salary int  
    )  

Insert Into ##SampleGlobalTemp
Select 'Jayant', 50000 Union All  
Select 'Jhon', 70000 Union All  
Select 'Ram', 33000  

Select * from  ##SampleGlobalTemp

When we run the script the output should like this. You can see that the inserted data are show and the temp table are stored in System Databases-> Tempdb-> Temporary Tables.

The scope of Global temp table exists to the all session and for all users. You can see below when I open another query window and access the global temp variable it can access the Global table and if you are login with another user also you can access that table.

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

When to Use Temporary Tables

1. We required to hold data from further query.
2. When we have a complex Join operation.
3. In some cases it may be a replacement of cursors.

Table Variables

Table Variables are also used for holding the data for a  temporary time just like Temp tables. Temp variables are created using “DECLARE” statements and are assigned values by using either a SET or SELECT command. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration. This acts like a variable and exists for a particular batch of query executions. It gets dropped once it comes out of batch. Temp variables are also created in the Tempdb database but not the memory.

DECLARE @Employee TABLE
 (
 empID INT IDENTITY(1,1),
 empName VARCHAR(50),
 Salary INT
 ) 
 --Insert data to Table variable @@Employee 
 INSERT INTO @EMPLOYEE(EMPNAME,SALARY)
 SELECT 'JAYANT',50000  UNION ALL
 SELECT 'RAM',20000     UNION ALL
 SELECT 'JHON',40000    UNION ALL
 SELECT 'LORI',55000    UNION ALL
 SELECT 'ROMAN',5000
 --Select data
 Select * from @EMPLOYEE Where SALARY> 20000
 
 --Next batch
 GO
 Select * from @EMPLOYEE --gives error in next batch

When we run the script it provides the result for the FIRST batch, Next batch will give the error because we terminate the scope using “GO” statement.

The above image shows when we run the script the first batch is executed and the Next batch gives error as the Table Variable property says Table variable can execute particular batch of query executions.

Limitations Of Temp Variables:

• Unlike the majority of the other data types in SQL Server, you cannot use a table variable as an input or an output parameter.
• The variable will no longer exist after the procedure exits – there will be no table to clean up with a DROP statement.
• We cannot create a non-clustered index on a table variable, unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table. It can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement. Indexes cannot be added after the table has been created.

When to use Temp Variables:

• When you are required to use the current result set in next queries then store result into temp variables.
• When your data set is smaller, approximately less than 1000 records, then use temp variables , if your result set is larger then use temp table instead of temp variables.

CTE

The CTE is an abbreviation of “Common Table Expression.” CTE was introduced in SQL Server. It works as a temporary result set that is defined within the execution scope of a single select, insert, update, delete statements. CTE is typically the result of complex sub queries. Similar to temporary tables CTE doesn’t store as an object; the scope is limited to the current query. CTE improves readability and ease in maintenance of complex queries and sub-queries.

SELECT E.EMPLOYEEID,E.FIRSTNAME,D.DEPTNAME,E.SALARY 
FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON D.DEPTID=E.EMPDEPT

When We run the script the Output Should like this:

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

;With CTE(EMPLOYEEID, FIRSTNAME, DEPTNAME,SALARY)--Column names for CTE, which are optional
AS
(
SELECT E.EMPLOYEEID,E.FIRSTNAME,D.DEPTNAME,E.SALARY FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON D.DEPTID=E.EMPDEPT
)
SELECT * FROM CTE WHERE SALARY>=50000--Using CTE

The Same query is modified to CTE with one condition whose salary is >= 50,000, When We run the script the Output Should like this:

CTE with recursion query

DECLARE @Min int;  
DECLARE @Max int;  
SET @Max = 10;  
SET @Min = 1;  
  
WITH Sequence_ AS  
  
(  
    SELECT @Min ASnum UNIONALL SELECT num + 1 FROM Sequence_ WHERE num + 1 <= @Max  
)  
SELECTnumFROM Sequence_

When we run the Script the output look like this. In the below example we use the recursive CTE and generated the sequence between 1 and 10.

When to use CTE

• Use for recursive query.
• CTE is easy to implement compared to complex queries which involves several sub-queries.

I hope, cover all the details about this topic, if still have any question or doubt you can add comments.