MSSQL: table variable vs temporary table

MSSQL T-SQL
Petr Kostelanský | 5 October 2017
Article describes differences between temporary tables and table variables in SQL server and their performance impact.

There is no simple to say if is better to use temporary table or table variable, because each of them is better for different purpose.

So let's see the differences:

Syntax

Temporary table:

Name of temporary table must start with #. If table start with ## it means global temporary table that is available for all sessions and users.

CREATE TABLE #TemporaryTable
(
	Id	INT,
	FirstName VARCHAR(50),
	Surname VARCHAR(50)
)

INSERT INTO #TemporaryTable (Id, FirstName, Surname) 
VALUES (1, 'FirstName1', 'Surname1'), (2, 'FirstName2', 'Surname2')

SELECT * FROM #TemporaryTable

DROP TABLE #TemporaryTable

Table variable:

DECLARE @TableVariable TABLE
(
	Id	INT,
	FirstName VARCHAR(50),
	Surname VARCHAR(50)
)

INSERT INTO @TableVariable (Id, FirstName, Surname) 
VALUES (1, 'FirstName1', 'Surname1'), (2, 'FirstName2', 'Surname2')

SELECT * FROM @TableVariable

Table modification

Temporary table:

Can be used commands CREATE, ALTER, DROP, TRUNCATE

Table variable:

 Table structure can't be change

Transactions

Temporary table:

Explicit transaction defined by user are supported. 

Table variable:

Isn't affected by transaction. So if you insert data into table variable in transaction and than call rollback data stay in table variable.

Indexes

Temporary table:

You can create indexes to increase performance. When you create index on temporary table statistic file for index will be also created. You can create index implicitly or explicitly. You can create cluster and non-clustered indexes.

Table variable:

You can create indexes just implicitly. You can create clustered and non-clustered (from SQL server 2014), but table variable doesn't have statistic distriution for indexes so the query optimizer assumes that table variable has one row and it could be performace issue for large datasets.

Scope visibility

Temporary table:

 Can be accesed within child batches (for example nested procedures)

Table variable:

 Can be accessed only in within batch and scope in which are declared.

Parallel operations

Temporary table:

Can be used for parallel operations.

Table variable:

Can not be used for parallel operations. Parallel execution plan is not generated for table variable.

Conclusion

It is recommended to use Table variables for storing datsets up to 100 rows and Temporary tables for bigger datasets. But it always depends on situation that developer solves.

Another interesting article about this topic:

Whats the difference between a temp table and table variable in sql server

Loading ads...