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.
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.
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.
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.
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.
CREATE TABLE #T
(
Id INT IDENTITY PRIMARY KEY CLUSTERED,
Name NVARCHAR(128),
EmailAddress NVARCHAR(50)
);
CREATE INDEX IX_T_EmailAddress ON #T (EmailAddress);
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.
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.
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.
DECLARE @T TABLE
(
Id INT IDENTITY PRIMARY KEY CLUSTERED,
Name NVARCHAR(128),
EmailAddress NVARCHAR(50)
);
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.
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.
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.
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
