Table variables are a commonly used feature in SQL Server, often assumed to be in-memory objects that don’t utilize anything from TempDB. However, there are some misconceptions about table variables that need to be clarified.

One of the questions that often arises is whether table variables are created as objects stored in TempDB and if they are written to the transaction log. To demystify this, let’s go through a step-by-step process.

First, let’s clear the transaction log of TempDB:

USE tempdb
GO
CHECKPOINT
GO

Next, we’ll create a table variable and check the number of rows added to the transaction log of TempDB:

DECLARE @TblVariable TABLE (
    id INT NOT NULL IDENTITY (1, 1),
    Name CHAR(30) NOT NULL,
    DOJ DATE NOT NULL,
    Company CHAR(400) NULL DEFAULT 'SQLAuth'
)

SELECT COUNT(*) [RowNums] FROM sys.fn_dblog(NULL, NULL)
GO

This simple experiment confirms that there is something written to the transaction log for table variable creation. We can also observe additional rows being added to the log when inserting rows into the table variable.

Now, let’s try to catch the table reference using the sysobjects:

SELECT * FROM sysobjects WHERE TYPE = 'U'

In this case, we won’t see any rows returned because table variables are not stored as user-defined tables in the sysobjects system table.

However, we can still access the data of a table variable by examining the metadata tables. To demonstrate this, consider the following script:

DECLARE @TblVariable TABLE (
    id INT NOT NULL IDENTITY (1, 1),
    Name CHAR(30) NOT NULL,
    DOJ DATE NOT NULL,
    Company CHAR(400) NULL DEFAULT 'SQLAuth'
)

INSERT @TblVariable (Name, DOJ) SELECT 'SQLAuth', GETDATE()

SELECT * FROM @TblVariable

WAITFOR DELAY '00:02:00'

In this script, we introduce a delay of 2 minutes to prevent the batch from finishing immediately. As a result, the table variable is not destroyed immediately, allowing us to query the sysobjects table and observe the table variable’s existence.

Furthermore, we can examine the data stored in the table variable by accessing the allocated page. Here is an example:

SELECT allocated_page_file_id, allocated_page_page_id, * 
FROM sys.dm_db_database_page_allocations(2, NULL, 1, NULL, 'DETAILED')
WHERE OBJECT_ID NOT IN (SELECT ID FROM sysobjects WHERE TYPE IN ('S', 'IT', 'SQ'))
AND page_type = 1
AND OBJECT_ID = -1098578155

The output of this query shows the allocated page for the data in the table variable. To further analyze the data, we can use the DBCC PAGE command:

DBCC TRACEON(3604)
GO
DBCC PAGE(2, 1, 306, 3)
GO

This confirms that table variables are indeed written to TempDB and can be viewed and debugged during their duration of existence, just like any normal table.

Understanding the behavior of table variables in SQL Server can help optimize their usage and avoid unexpected resource consumption in TempDB. If you have ever had the chance to view and debug table variables like this, feel free to share your experiences.