Your browser (Internet Explorer 7 or lower) is out of date. It has known security flaws and may not display all features of this and other websites. Learn how to update your browser.

X

Temp tables or table variables

Some Misconceptions and main differences

Every time I perform a code review or read some blog posts that use temp tables or temp variables I find that the difference between temp tables and temp variables is not well understood. The common misconception for example is that both are created in memory, the other is that it is recommended to use temp tables instead of table variables (vise versa is also common), or people just prefer to use one or another temp object without knowing if it is appropriate for specific case scenario.

Here are some examples in order to clarify the major differences.

Where are the temp tables and table variables created?

Temp tables and table variables are both objects created INSIDE TEMPDB. They are not created in memory. Their presence in memory is a result of their usage. Meaning: their pages are transferred and stored inside the buffer pool because this is the common way SQL Server works with all data pages – when a data page is requested for reads or writes it is read from disk and saved in the Buffer pool. So unless they are too big or server memory is under pressure they will be cached in memory anyway.

Here is an example:

Let’s check the sys.dm_db_file_space_usage. Because I am working with SQL Server 2012, I am executing this DMV in user db and in tempdb in order to show you some slight differences in SQL Server 2012:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
USE AdventureWorks2012
GO
SELECT database_id, SUM(unallocated_extent_page_count) AS FreeSpacePages,
SUM(user_object_reserved_page_count) AS UserObjectPages,
SUM(internal_object_reserved_page_count) AS InternalObjectPages,
SUM(version_store_reserved_page_count) AS VersionStorePages,
SUM(mixed_extent_page_count) AS MixedExtentPages
FROM sys.dm_db_file_space_usage
GROUP BY database_id
GO

USE tempdb
GO
SELECT database_id, SUM(unallocated_extent_page_count) AS FreeSpacePages,
SUM(user_object_reserved_page_count) AS UserObjectPages,
SUM(internal_object_reserved_page_count) AS InternalObjectPages,
SUM(version_store_reserved_page_count) AS VersionStorePages,
SUM(mixed_extent_page_count) AS MixedExtentPages
FROM sys.dm_db_file_space_usage
GROUP BY database_id
GO

Prior to SQL Server 2012 the sys.dm_db_file_space_usage was applicable only to tempdb (database_id was always 2). Actually User Object pages, Internal Objects Pages and Version Store Pages are still applicable for TempDB Only (they are NULL when the db is not tempdb). But FreeSpacePages and MixedExtends as well as other columns show database context values.

I am creating temp table and loading it with 1000 pages of data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
USE AdventureWorks2012
GO

CREATE TABLE #MyTempTable
( ID INT,
ColA CHAR(8000)
);
GO

DECLARE @i INT = 0;

WHILE (@i < 1000)
BEGIN
INSERT INTO #MyTempTable VALUES(@i,REPLICATE('A',100));
SET @i += 1;
END;

Let’s check the sizes of UserObjectPages now:

We have increased UserObjectPages by 1000 in tempdb. As you see the values in db6 have no changes. So the temp table is created as an user object inside the tempdb.

The same is happening with table variables:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
USE tempdb
GO

DECLARE @MyTempTable TABLE (ID INT, ColA CHAR(8000));

INSERT INTO @MyTempTable (ID, ColA)
SELECT ID, colA FROM #MyTempTable;
SELECT database_id, SUM(unallocated_extent_page_count) AS FreeSpacePages,
SUM(user_object_reserved_page_count) AS UserObjectPages,
SUM(internal_object_reserved_page_count) AS InternalObjectPages,
SUM(version_store_reserved_page_count) AS VersionStorePages,
SUM(mixed_extent_page_count) AS MixedExtentPages
FROM sys.dm_db_file_space_usage
GROUP BY database_id
GO

The UserObjectPages increased by 1000. If I check the size again in a new batch, the pages of table variables will be DE allocated:

 

Do they have metadata?

Let’s check

1
2
3
DECLARE @MyTable TABLE(ID INT, Description CHAR(8000));

SELECT * FROM sys.COLUMNS WHERE name = 'Description' OR name='ColA'

They both have metadata, because they both are physical objects stored inside the tempdb. So definitely temp tables and table variables are physical objects created and stored inside tempdb

How they behave inside the transactions?

Let’s see. The cases are from Greg Low’s scripts. I am inserting rows in temp table and in table variable inside an explicit transaction. I am getting the locking state during the transaction and then rolling back the transaction:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
USE tempdb;
GO

CREATE TABLE dbo.SOURCE
( ID INT,
Description CHAR(100)
);
GO

DECLARE @Counter INT = 0;

WHILE (@Counter < 100)
BEGIN
INSERT INTO dbo.SOURCE VALUES(@Counter,REPLICATE('G',100));
SET @Counter += 1;
END;

CREATE TABLE #Target (ID INT, Description CHAR(100));
GO

BEGIN TRAN
INSERT INTO #Target
SELECT ID, Description
FROM dbo.SOURCE;

DECLARE @Target TABLE(ID INT, Description CHAR(100));

INSERT INTO @Target
SELECT ID, Description FROM dbo.SOURCE;

SELECT tl.resource_type, tl.resource_database_id,
tl.resource_associated_entity_id, tl.request_mode,
tl.request_type, tl.request_status, tl.request_session_id
FROM sys.dm_tran_locks tl LEFT OUTER JOIN sys.dm_os_waiting_tasks wt
ON tl.lock_owner_address=wt.resource_address
ROLLBACK;

SELECT COUNT(*) AS TemptableTargetCount FROM #Target;
SELECT COUNT(*) AS tempVarTargetCount FROM @Target;

GO

The locking data during the transaction, the content if the temp table and table variable are shown below:

The scope of table variables is a batch, stored procedure, UDF. Table variables “resist” transaction rollbacks, i.e. rollback is not returning back the DML that is applied to table variables.

Locking behaviour of temp tables and table variables are slighly different, which doesn’t mean that table variables do not use locks at all. They just do not hold locks until the end of the explicit transaction, but only of the duration of the DML statement. The statement above shows locks that resists by the end of transactions and those are on temp table. I am not going to go into further details as this is not the purpose  of this post. You can reffer to very comprehensive Martin Smith post

The scope of temporary tables is a session and they behave like regular tables, the rollback result is as expected.

 

What is their logging behavior?

I am omitting the ddl code for both objects and I am copying directly the update statements:

1
2
3
4
5
6
7
8
UPDATE @Target SET Description = REPLICATE('T',100);

--Let’s check log records created inside the log for both cases

SELECT TOP 10 operation, context, [log record fixed LENGTH],
[log record LENGTH], AllocUnitId, AllocUnitName
FROM fn_dblog(NULL, NULL)
ORDER BY [Log Record LENGTH] DESC;
1
2
3
4
5
6
7
8
9
10
11
UPDATE #Target SET Description = REPLICATE('T',100);

--Let’s check log records created inside the log for both cases

SELECT TOP 10 operation, context, [log record fixed LENGTH],
[log record LENGTH], AllocUnitId, AllocUnitName
FROM fn_dblog(NULL, NULL)
WHERE AllocUnitName LIKE '%Target%'
ORDER BY [Log Record LENGTH] DESC;

GO

Updating temp tables and table variables creates logging records. The logging in tempdb s the same in both cases. The tempdb has a special logging behaviour because it is not recovered, so llogging is not needed in the form that we know about. You can read more about tempdb logging here.

What I can do with temp tables and table variables?

With Table Variables like with temp tables you can:

  • Populate with INSERT ..SELECT
  • Use in batches, stored procedures, and user-defined functions (UDFs).
  • UPDATE records as well as DELETE records.

BUT

The major difference between temp tables and table variables is that statistics are not created on table variables. This has two major consequences, the first of which is that the Query Optimizer uses a fixed estimation for the number of rows (it estimates one row) in a table variable irrespective of what data is in it. Moreover, adding or removing data doesn’t change the estimation.

You can’t create indexes on table variables although you can create constraints. This means that by creating primary keys or unique constraints, you are able to have indexes (as these are created to support constraints) on table variables. Even if you have constraints and therefore indexes that will have statistics, the indexes will not be used when the query is compiled because they won’t exist at compile time, nor will they cause recompilations.

Schema modifications are possible on temporary tables but not on table variables. While schema modifications are possible on temporary tables, avoid using them because they will cause recompilations

of statements that use the tables.

Here is the comparison:

 

Summary

Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure because of statistics updates (they have very low update trescholds) resulting in invalidating plans (in  most of the cases, not in SQL Server 2012, but this is a topic of another coming post). You can use KEEP PLAN hint to change this behaviour.

Table variables can often avoid this recompilation hit, because they don’t have statistics BUT can lead to cardinality errors, and in this cases in order to gain the optimal execution plan a RECOMPILE hint or OPIMIZE hint could work.

Temp variables are kind of more economical in case of locking. But are not so flexible according to schema modifications, insert ..exec and dynamic sql child routines.

Temp table could make you life terrible if the tempdb collation differs from your user db collation in case you have JOINS between temp tables and your database tables. In this case you need to use COLLATE per JOIN column. Some changes apply to SQL Server 2012 and contained databases.

Hopefully it is more clear now and you can find your case of using either temp table or table variables.

Thanks for reading