Transaction Logging - Table Variable and Temporary Table

You may have already known some of the difference between table variable and temporary table like ability to create statistics, execute in parallel plan (for update query), transaction rollback and other limitations. This post will be focusing on their transaction logging resources.

This knowledge base article and TechNet article list out table variables characteristic and its difference with temporary table as well as suggestion of when to use it. Another article from sqlservercentral by Wayne Sheffield also list out their difference side by side with some of the myths.

We will be focusing on its transaction logging in this post. Microsoft article states that the table variables require less logging resources than temporary table.

Let's do some test. First, we create a stored procedure with table variable creation and insert one row.

USE tempdb;
GO

IF (OBJECT_ID('tempdb.dbo.Proc1') IS NOT NULL)
BEGIN
 DROP PROCEDURE dbo.Proc1;
 --Allow time for cached object to be removed
 WAITFOR DELAY '00:00:05';
END
GO

CREATE PROCEDURE dbo.Proc1
AS
BEGIN
 DECLARE @tbl1 TABLE (num int);
 
 INSERT INTO @tbl1 (num)
 VALUES (0);
END
GO

--Cache object and initialization
EXEC Proc1;
GO

--Truncate log 
CHECKPOINT;
GO

EXEC PROC1;
GO

SELECT
    f.[Current LSN],
    f.Operation,
 f.Context,
    f.AllocUnitName,
    f.[Transaction Name],
    f.[Transaction ID]
FROM sys.fn_dblog(NULL, NULL) AS f;

I put a delay of 5 seconds to allow amber time for the cache objects to be removed and prevent those removing log transaction mix up with the transaction log for this demo.

Here is how the transaction logs look like.



Next, we do the exact same thing but this time with temporary table.

USE tempdb;
GO

IF (OBJECT_ID('tempdb.dbo.Proc1') IS NOT NULL)
BEGIN
 DROP PROCEDURE dbo.Proc1;
 --Allow time for cached object to be removed
 WAITFOR DELAY '00:00:05';
END
GO

CREATE PROCEDURE dbo.Proc1
AS
BEGIN
 CREATE TABLE #tbl1 (num int);
 
 INSERT INTO #tbl1 (num)
 VALUES (0);
END
GO

--Cache object and initialization
EXEC Proc1;
GO

--Truncate log 
CHECKPOINT;
GO

EXEC PROC1;
GO

SELECT
    f.[Current LSN],
    f.Operation,
 f.Context,
    f.AllocUnitName,
    f.[Transaction Name],
    f.[Transaction ID]
FROM sys.fn_dblog(NULL, NULL) AS f;

There are more transactions required when using temporary table. The result from this example shows that table variables does indeed use less logging resources.

If we change the script from inserting one row to three rows with a while loop, the table variable transaction log looks like this,

DECLARE @tbl1 TABLE (num int);
DECLARE @num int = 0;

WHILE (@num < 3)
BEGIN
 INSERT INTO @tbl1 (num)
 VALUES (@num);
 
 SET @num += 1;
END

And for temporary table, it looks like this,

CREATE TABLE #tbl1 (num int);
DECLARE @num int = 0;

WHILE (@num < 3)
BEGIN
 INSERT INTO #tbl1 (num)
 VALUES (@num);
 
 SET @num += 1;
END

The transaction for insert statement for both table variable and temporary table appear to be the same. If you notice, every insert entry is wrap within a transaction. This is because by default, SQL Server operates in autocommit transaction mode, and every insert statement is treated as a transaction.

What happen if we change it to explicit transaction? This time let's examine the transaction log for temporary table first.

USE tempdb;
GO

IF (OBJECT_ID('tempdb.dbo.Proc1') IS NOT NULL)
BEGIN
 DROP PROCEDURE dbo.Proc1;
 --Allow time for cached object to be removed
 WAITFOR DELAY '00:00:05';
END
GO

CREATE PROCEDURE dbo.Proc1
AS
BEGIN
 CREATE TABLE #tbl1 (num int);
 DECLARE @num int = 0;

 BEGIN TRAN;

 WHILE (@num < 3)
 BEGIN
  INSERT INTO #tbl1 (num)
  VALUES (@num);
 
  SET @num += 1;
 END
 
 COMMIT;
 DROP TABLE #tbl1;
END
GO

--Cache object and initialization
EXEC Proc1;
GO

--Truncate log 
CHECKPOINT;
GO

EXEC PROC1;
GO

SELECT
    f.[Current LSN],
    f.Operation,
 f.Context,
    f.AllocUnitName,
    f.[Transaction Name],
    f.[Transaction ID]
FROM sys.fn_dblog(NULL, NULL) AS f;

With explicit transaction mode (Begin Tran), all three insert statements are wrapped into one transaction. This is expected.

What about table variable?

USE tempdb;
GO

IF (OBJECT_ID('tempdb.dbo.Proc1') IS NOT NULL)
BEGIN
 DROP PROCEDURE dbo.Proc1;
 --Allow time for cached object to be removed
 WAITFOR DELAY '00:00:05';
END
GO

CREATE PROCEDURE dbo.Proc1
AS
BEGIN
 DECLARE @tbl1 TABLE (num int);
 DECLARE @num int = 0;

 BEGIN TRAN;

 WHILE (@num < 3)
 BEGIN
  INSERT INTO @tbl1 (num)
  VALUES (@num);
 
  SET @num += 1;
 END
 
 COMMIT;
END
GO

--Cache object and initialization
EXEC Proc1;
GO

--Truncate log 
CHECKPOINT;
GO

EXEC PROC1;
GO

SELECT
    f.[Current LSN],
    f.Operation,
 f.Context,
    f.AllocUnitName,
    f.[Transaction Name],
    f.[Transaction ID]
FROM sys.fn_dblog(NULL, NULL) AS f;

Even with explicit transaction mode (Begin Tran), each insert statement is treated as its own transaction as if it is in autocommit transaction mode.

Referring back to Microsoft article, that transaction involve table variable last only for the duration of an update on the table variable and is not affected by transaction rollback. This appears to be in line with the the above observation that the each insert statement is treated as its own transaction, and explicit transaction rollback will have no effect on it.

I run a few tests by inserting 1 million rows. Without explicit transaction mode, table variable method generates slightly less transaction log and run slightly faster than temporary table method. However in explicit transaction mode, temporary table method ends up with about 3 times less the transaction logs and averagely run half the time it takes for table variable method to complete the process.

From the tests above, we verify that the table variable require less transaction logging but at the same time each insert/update statement is treated as its own transaction which increase transaction logging. As the golden rule, test it to determine which temporary storage is the best for your environment.