Click here to Skip to main content
15,867,488 members
Articles / Database Development / SQL Server

Temporary Tables and Temporary Variables

Rate me:
Please Sign up or sign in to vote.
5.00/5 (6 votes)
31 May 2015CPOL7 min read 21.9K   8   4
Compare likenesses and differences between Temporary Tables and Temporary Variables

Introduction

In T‑SQL we often need some way how to temporarily work with data. Temporary tables and variables are broadly used for this purpose.

There is a big confusion around choosing appropriate type and moreover there are many myths regarding the differences between these two of them. In this paper I’d like to show you likenesses and differences and help you to choose right temporary data object suitable to your purpose.

Tempdb Database

Both temporary tables and temporary variables are always created in tempdb database. One of the myth is that temporary objects are created in memory. This is incorrect. Look on example.

SQL
SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#%';
GO

CREATE TABLE #T(I INT);
DECLARE @T TABLE(I INT);

SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#%';
GO

DROP TABLE #T;

First output is empty. Second output returns two rows, where first row represents temporary variable and second row temporary table. Obviously you will probably get different names when you run code above.

#B955E09D #T__________________________________________________________________________________________________________________0000000000D8

Very important fact is that recovery model of tempdb database is SIMPLE. This leads us to optimizations that we can benefit. All bulk operations use minimal logging. Also when we drop a temporary object, MS SQL Server drops database in background. So your code can immediately continue. Because tempdb is newly created after each start of server, there’s no need for any recovery process. Transaction log doesn’t need to be flushed to disk. consequently transactions in tempdb are actually committed faster.

Caching

MS SQL Server supports caching for temporary objects (including temporary tables and temporary variables) referenced in stored procedures for reuse. If the object is smaller than 8 MB, MS SQL Server keeps object, and reuses this one instead of allocating a new one for next time. But if the object is larger than 8 MB, then it is dropped on background thread and control is immediately returned to the application. This feature reduces tempdb allocation structures and catalogue tables. It also results in faster creating and dropping temporary objects.

SQL
IF OBJECT_ID('dbo.TestCachingOfTemporaryObjects', 'P') IS NOT NULL
    DROP PROC [dbo].[TestCachingOfTemporaryObjects];
GO

DBCC FREEPROCCACHE;
WAITFOR DELAY '00:00:10';
GO

CREATE PROCEDURE [dbo].[TestCachingOfTemporaryObjects]
AS  
	CREATE TABLE #T(I INT);
GO

SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';

EXEC [dbo].[TestCachingOfTemporaryObjects];

SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';

First result should be now empty and second one contain cached object. I got this output #AB578D27, but name will vary.

Temporary objects are removed from this cache when tempdb hasn’t enough space or execution plan is recompiled or removed from cache. Also number of things can prevent temporary tables (not temporary variables) from caching.

  • Create named constraints
  • Apply DDL to temporary table after creation
  • Creation using dynamic T‑SQL
  • Table created in a different scope
  • Procedure executed WITH RECOMPILE

Temporary Tables

MS SQL Server supports two types of temporary tables - local and global one. Local temporary table names are prefixed with a hash sign (#) and global temporary tables by prefixing two hash signs (##). As name refers, global temporary tables differ from local ones mainly in scope and visibility. They are accessible by all sessions with no security limitations. For now we’ll focus only on local temporary tables.

SQL
CREATE TABLE #T (...);

Scope and Visibility

A local temporary table is created and visible on session level. So local temporary table is visible only to the current and inner levels. Local temporary tables on some level are not visible to any outer level. These tables are destroyed as soon as soon as all code in creating level finishes.

MS SQL Server allows you create local temporary table with same name in different sessions. To distinguish between them, names get underscores and unique suffixes.

SQL
CREATE TABLE #T (I INT);

SELECT * FROM tempdb.sys.objects WHERE name LIKE '#T%';

DROP TABLE #T;

The suffix will vary, but you will find name similar to #T__________________________________________________________________________________________________________________00000000001B.

Transaction Context

Remember that tempdb database is always in SIMPLE mode. Consequently no recovery process is needed. Logging has to support only roll back operations. Roll forward operations are not necessary. Also from previous paragraph we know that temporary table is created on session level. This fact has consequences for less locking involved than permanent tables. Actually permanent tables can be accessed from multiple sessions.

Statistics

The important fact is that optimizer maintains statistics for temporary tables. In many ways it is very similar to permanent tables. Statistics are then used by optimizer to choose appropriate query plan. This can significantly improve performance when you index temporary table. This is one of the most important difference from table variables. Table variables do not maintain statistics.

On the other hand because of maintaining statistics, table must be sometimes recompiled. Recompilation of table can be triggered by reaching recompilation threshold, updating statistics, etc. A recompilation threshold (RT) is calculated based on the cardinality of a referenced table.

  • Permanent table
    • If n <= 500, RT = 500.
    • If n > 500, RT = 500 + 0.20 * n.
  • Temporary table
    • If n < 6, RT = 6.
    • If 6 <= n <= 500, RT = 500.
    • If n > 500, RT = 500 + 0.20 * n.
  • Table variable
    • RT does not exist. Therefore, recompilations do not happen.

As you can see recompilation happens less often for permanent tables.

SQL
CREATE TABLE #T
(
	Id INT IDENTITY PRIMARY KEY CLUSTERED,
	Name NVARCHAR(128),
	EmailAddress NVARCHAR(50)
);

CREATE INDEX IX_T_EmailAddress ON #T (EmailAddress);

-- Create some test data.
WITH Numbers(I) AS
(
    SELECT 1
    UNION ALL 
    SELECT I + 1
    FROM Numbers
    WHERE I < 1000
)
INSERT #T
SELECT 
	'Name' + CAST(I AS VARCHAR),
	'Email' + CAST(I AS VARCHAR) + '@test.com'
FROM Numbers
OPTION (MAXRECURSION 0);

SELECT Name
FROM #T
WHERE EmailAddress = 'Email999@test.com';

DROP TABLE #T;

For this query MS SQL Server uses statistics for non‑clustered index IX_T_EmailAddress. Based on this, Index Seek operation is ran and afterwards column Name is fetched from clustered key.

Image 1

Table Variables

Hard to say why but table variables is one of the least understood topic in MS SQL. One of the biggest myth is that table variables are stored in memory without any physical representation. This is not correct. We already showed in tempdb database section that they have physical representation.

SQL
DECLARE @T TABLE(...);

One interesting fact is that table‑valued parameters are internally implemented like table variables. So all discussion below regarding table variables apply also to table‑valued parameters.

Limitations

Table variables have some important limitations.

  • Until MS SQL Server 2012 only PRIMARY KEY and UNIQUE constrains could be created. You cannot create any non‑unique indexes. This was changed in MS SQL Server 2014 where you can now use non‑unique clustered and non‑clustered indexes.
  • Definition of table variables cannot be changed after it is declared.
  • When modifying a table variable, the MS SQL Server storage engine never generate a parallel execution plan. Only read queries can be parallelized.

Scope and Visibility

As name indicates, the scope of table variables is defined on the current level and within current batch. A table variable is not accessible in inner levels, and not to other batches. Basically scope is same like usual variable.

Transaction Context

When you modify table variable and modification is for some reason aborted, these changes are obviously reverted. You can be sure that your table will stay in same state as before. Nevertheless, if table variable is part of transaction that is rolled back, these changes won’t be rolled back. Local variables are not affected with transaction. This is correct behavior because table variable represent a variable, not a table.

Because of this, table variables involve much less logging and locking than temporary tables inside transaction context.

Statistics

Very crucial fact is that optimizer doesn’t maintain statistics for table variables. The downside of this is that you might not get efficient plans. This is mainly problem for big tables where we can reach high IO reads. But on the other hand this is also big advantage. Since missing maintaining statistics, we end up in less recompilations of table and faster access.

During creating execution plan, optimizer always assumes that there is only 1 row in your table. Remember in some cases this can have big effect on you queries.

SQL
DECLARE @T TABLE
(
	Id INT IDENTITY PRIMARY KEY CLUSTERED,
	Name NVARCHAR(128),
	EmailAddress NVARCHAR(50)
);

-- Create some test data.
WITH Numbers(I) AS
(
    SELECT 1
    UNION ALL 
    SELECT I + 1
    FROM Numbers
    WHERE I < 1000
)
INSERT INTO @T
SELECT 
	'Name' + CAST(I AS VARCHAR),
	'Email' + CAST(I AS VARCHAR) + '@test.com'
FROM Numbers
OPTION (MAXRECURSION 0);

SELECT Name
FROM @T
WHERE Name LIKE '%';

Execution plan is basically what we expect – it uses Clustered Index Scan.

Image 2

But surprise come in detail view. Estimated number of rows is equal to 1 and Actual number of rows is equal to 1000. This is debt for lack of statistics. It is important to realize this fact when you choose table variable or temporary table.

Image 3

In some situations you can use OPTION (RECOMPILE) but obviously this is not a lifesaver. You should always think twice when you use any table hint.

SQL
SELECT Name
FROM @T
WHERE Name LIKE '%'
OPTION (RECOMPILE);

Summary Table

Functionality Local Temporary Table Table Variable
Scope and Visibility Current and inner levels Current level
Physical representation Yes Yes
Cached definition Yes (with some limitations) Yes
Part of outer transaction Yes No
Logging and locking To support roll back operation To support statement roll back operation
Maintaining statistics Yes No
Recompilations Yes No
Efficient plan Yes No
Parallel execution plans Yes Only reads
Non-unique indexes Yes From MS SQL Server 2014

References

History

  • Initial release v1.0: 1st June 2015

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer Barclays Capital
Czech Republic Czech Republic
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionVery nice overview! Pin
Christophe Van Olmen2-Jun-15 2:46
professionalChristophe Van Olmen2-Jun-15 2:46 
AnswerRe: Very nice overview! Pin
Milan Matějka2-Jun-15 2:56
Milan Matějka2-Jun-15 2:56 
Thank you very much. It is very good point actually. I am sure I will add it in next update of article. Thanks.
AnswerRe: Very nice overview! Pin
Liju Sankar17-Jul-15 6:17
professionalLiju Sankar17-Jul-15 6:17 
GeneralRe: Very nice overview! Pin
Milan Matějka20-Jul-15 7:54
Milan Matějka20-Jul-15 7:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.