Temp Table vs Table Variable vs CTE in SQL Server

SQL Server Temp Table vs Table Variable vs CTE

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) <= 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.

Temp Table vs Table Variable vs CTE

Sl.# Temp Table Table Variable CTE
1 Scope wise the local temp table is available only in the current session.

The global temp tables are available for all the sessions or the SQL Server connections.

The scope of the table variable is just within the batch or a view or a stored procedure. The scope of the CTE is limited to the statement which follows it.
2 Temp tables are stored in TempDB. Table variables are also stored in TempDB. The result set from CTE is not stored anywhere as that are like disposable views.
3 The name of the temp table can have only up to 116 characters. The name of the table variable can have up to 128 characters. Not Applicable
4 Considering the performance, it is recommenced to use temp table for storing huge data, say more than 100 rows. Table variable is recommended for storing below 100 rows. No such performance consideration. CTE normally used as a replacement for complex sub queries.
5 The structure of temp table can be altered after creating it. The structure of table variable cannot be altered. The definition of CTE cannot be changed during run time.
6 Can explicitly drop temp tables using DROP statement. Cannot drop table variable explicitly. Cannot be dropped.
7 Cannot be used in User Defined Function (UDF). Can be used in UDF. Can be used in UDF.
8 Temp tables take part in transactions. Table variables wont take part in transactions. Not Applicable
9 Index can be created on temp tables. Index is not possible on table variables. CTE cannot be indexed.
10 Can apply read lock on temp tables. Locking is not possible in table variables. Locking is not possible in CTE as well.
11 Constraints can be created on temp tables except FOREIGN KEY. PRIMARY KEY, UNIQUE KEY and NULL are the only constraints allowed in table variable. CTE cannot have constraints.

Related

Reference:

  • Details about temporary tables at MSDN.
  • About table variable at MSDN.
  • Details about CTE at MSDN.


5 thoughts on “Temp Table vs Table Variable vs CTE in SQL Server”

  1. only #temp tables have statistics.. for performance reasons with complex queries, the statistics are critical in getting a decent query plan.

    Reply
  2. TVVs are not stored in memory unless created as memory tables (2014 enterprise, 2016 and above)
    Performance of TVVs only appears to differ from Temp tables in the following circumstances in my testing (versions 2014 & 2016 – standard editions)
    1) when indexing not supported by TVV in version is required
    2) when truncate can be used instead of delete for a Temp table
    3) when drop can be used to free resources for a temp table
    4) when creating indices after table creation will benefit

    If these more advanced management options are not required the greater convenience of TVVs should make them the preferred option

    Reply
  3. Data in table variables are held in memory, not in TempDB. Only when data is huge (for which you would rather use temp table as you correctly mentioned) does it automatically spill to TempDB.

    Reply

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.