Temp Table vs Table Variable vs CTE in SQL Server

I’m often getting questions on when to use the Temp Table, CTE (Common Table Expression) or the Table Variable In SQL Server. So, I thought of writing an article about these three temporary storage units and list down the differences between Temp Table vs Table Variable vs CTE.

Temp Table

Temp tables are otherwise called as hash tables or temporary tables.The name of the temp table starts with single number sign (#) or double number sign (##). The temporary tables are created during the execution time. As the name suggests, temp tables are available only in the scope where they are created. They are stored in the TempDB and will get dropped automatically after the scope. The name of the temp tables can have a maximum of 116 characters. another limitation of temp table compared with their physical counterpart is that they cannot be partitioned.

Temporary tables are of two types, Local Temporary Tables and Global Temporary Tables. This classification is based on the scope of their existence. Let’s see them in detail.

1. Local Temp Table

The local temp table’s name is prefixed with single number sign (#) (Example: #TableName). These local temp tables are available only in the current session. I.e. if you create a hash table in a stored procedure, then the table will be available only for that stored procedure or any other nested stored procedures called from inside that stored procedure. Once the stored procedure finishes execution, the hash table drops automatically from the TempDB. So only SQL user/connection which created the temp table alone can use it.

Syntax Of Local Temp Table

CREATE TABLE #EmployeeLocal
(
 EmpID int,
 EmpName varchar(100), 
 EmpAddress varchar(300),
 EmpDOB Date
)
GO
INSERT INTO #EmployeeLocal values ( 235, 'John Doe','Minneapolis, MN', '1975-03-01');
GO
SELECT * FROM #EmployeeLocal

Usage Of Local Temp Table

You can use local temp tables:

To store temporary data.
If the size of the temporary data is huge, say more than 100 rows.
When the user or connection which creates them alone can use it.
If you want to use explicit transactions against the temporary data.
When you may need to create indexes.
If you may need to apply read lock.
You CANNOT use temp tables in User Defined Functions (UDF).

2. Global Temp Table

The global temp table’s name is prefixed with double number sign (##) (Example: ##TableName). The global temp tables are available for all the sessions or the SQL Server connections. I.e. Multiple SQL Server users can use the same temp table. The table exists till the creates session and all the other sessions or connections using the global temp table closes. Once all the sessions and connections stops using the global temp table, it will automatically drops from the TempDB.

Syntax Of Global Temp Table

CREATE TABLE ##EmployeeLocal
(
 EmpID int,
 EmpName varchar(100), 
 EmpAddress varchar(300),
 EmpDOB Date
)
GO
INSERT INTO ##EmployeeLocal values ( 235, 'John Doe','Minneapolis, MN', '1975-03-01');
GO
SELECT * FROM ##EmployeeLocal

Usage Of Global Temp Table

You have to use global temp table, if you want to use the advantages of local temp table plus the feature of multiple users / sessions / connections should have access to the same table.

Table Variable

Table variable is a special kind of data type. It has most of the features of a normal variable along with the capability of storing a result set. This stored result set can be used within a batch. Just like temp table, table variable is also stored in TempDB.

The table variable needs to be declared just like a variable. While declaring, we have to specify the column details.

Syntax Of Table Variable

GO
DECLARE @EmployeeLocal TABLE
(
 EmpID int,
 EmpName varchar(100), 
 EmpAddress varchar(300),
 EmpDOB Date
)

INSERT INTO @EmployeeLocal values ( 235, 'John Doe','Minneapolis, MN', '1975-03-01');

SELECT * FROM @EmployeeLocal
GO

Usage Of Table Variable

You have to use table variable only:

To store temporary data in user defined functions (UDF), stored procedures and query batches. In fact it is the means of returning result set in table-valued user defined functions.
If the volume of data is less, say less than 100 rows. Microsoft recommends to use Temp Table if you have more than 100 rows of data. Because the optimizer in some cases may ignore the number of records in table variable while generating the query plan.
When you want to use the temporary data just like referencing a table in SELECT, INSERT, UPDATE and DELETE query.
If you don’t want to have transactions against the temporary data.
When you don’t need to alter the table structure after creating it.

CTE (Common Table Expression)

Common Table Expression or CTE is a temporary result set generated from SELECT query defined by WITH clause. The scope of the common table expression is limited to the SELECT, UPDATE, INSERT or DELETE statement which is immediately following it.

There are two types of Common Table Expression Non-Recursive CTE and Recursive CTE.

1. Non-Recursive CTE

Non-recursive common table expression is the generic form of CTE. It does not have any reference to itself in the CTE definition.

2. Recursive CTE

When a CTE has reference in itself, then it’s called recursive CTE.

Syntax Of CTE

;WITH EmployeeBornBefore1985_CTE (EppID, EmpName, YearOfBirth)  
AS  
(  
    SELECT EppID, EmpName, YEAR(EmpDOB) AS YearOfBirth  
    FROM EmployeeMaster  
    WHERE YEAR(EmpDOB) &amp;amp;amp;amp;amp;amp;amp;amp;lt;= 1985
)   
SELECT
	EppID, EmpName, YearOfBirth  
FROM 
	EmployeeBornBefore1985_CTE  
WHERE 
	EMPName Like 'C%' 
GO

Usage Of CTE

CTE is usually used to temporarily store the result set of a complex sub-query, so as you can use the result set later in the main query.