So, it’s the second Tuesday of the month again, and it’s time for T-SQL Tuesday again. This month it’s hosted by Sankar Reddy and the topic is “Misconceptions in SQL Server”

I thought I’d tackle a trio of table variable myths and partial truths.

Table Variables are memory-only

This one is pervasive and irritating. It typically goes like this:

You should use table variables rather than temp tables because table variables are memory only.

This myth can be broken down into two parts:

1. That table variables are not part of TempDB
2. That table variables are not written to disk

The first is easy to prove and has been done repeatedly. I’m not doing it again. I’m going to tackle the second portion only.

See, one could argue that, even though the table variable is created in the TempDB system tables and allocated pages within the TempDB data file, it is still kept entirely and only in memory. Let’s see if that’s true…

DECLARE @LargeTable TABLE (
id INT IDENTITY PRIMARY KEY,
LargeStringColumn1 CHAR(100),
LargeStringColumn2 CHAR(100)
)
INSERT INTO @LargeTable (LargeStringColumn1, LargeStringColumn2)
SELECT TOP (100000) 'Table Variable Test','T-SQL Tuesday!'
FROM master.sys.columns a CROSS JOIN master.sys.columns b
WAITFOR DELAY '00:01:00' -- so that the table var doesn't go out of scope and get deallocated too quickly.

This is not a massively large table. 100000 rows at 204 bytes per row (excluding header). A query of sys.dm_db_index_physical_stats (which does work on temp tables and table variables) reveals a total page count of 2632. That’s a grand total of 20.6 MB. 20 Megabytes. The SQL instance I’m running this on is allowed to use up to 2 GB of memory. No way on earth is this table variable going to cause any form of memory pressure (and I promise there is nothing else running)

So, run that code and, while that waitfor is running, do something that should never be done to a SQL server that you care anything about.

That’s going to kill SQL so fast that it’s not going to have a chance to clean up or deallocate anything on the way out. Just how I want it.

Now load up my favourite hex editor and open the TempDB data file and see if any rows from the table variable are there.

That pretty much speaks for itself. This myth, clearly false.

Table Variables cannot be indexed

Not too common, but I have seen this one floating around. It typically goes something like this:

Table variables cannot have indexes created on them. The only exception is a clustered index defined as part of the primary key.

Now there’s a small grain of truth in this. Both of the following return an error

DECLARE @SomeTable TABLE (
ID int,
SomeColumn VARCHAR(20)
)
ALTER TABLE @SomeTable ADD CONSTRAINT pk_SomeTable PRIMARY KEY CLUSTERED (id)

DECLARE @SomeTable TABLE (
ID int,
SomeColumn VARCHAR(20)
)
CREATE INDEX idx_Testing ON @SomeTable (SomeColumn)

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near ‘@SomeTable’.

Ok, so it’s not possible to run a CREATE INDEX or ALTER TABLE statement against a table variable, but does that mean that it’s limited to a single clustered index (defined as part of the primary key?)

It does not.

Firstly, there’s no requirement that the primary key be enforced by a clustered index. The following is perfectly valid.

DECLARE @Test TABLE (
ID INT NOT NULL PRIMARY KEY NONCLUSTERED,
SomeCol VARCHAR(20)
)

A query against TempDB’s system tables with that table declared clearly shows two entries in sys.indexes for that table variable, index id 0 (the heap) and a single non-clustered index with an auto-generated name indicating that it is enforcing the primary key

So does that mean that we can have one and only one index on a table variable?

Again, no.

We’re limited to creating any desired indexes as part of the table’s definition, but there are two constructs that can be defined that way. Primary key and unique constraints. We can define as many unique constraints as desired on a table variable (up to the limit of number of indexes on tables). If the columns that need to be indexed aren’t unique themselves, we can always add the primary key column(s) to the unique constraint so that the combination is always unique.

DECLARE @Test TABLE (
ID INT NOT NULL PRIMARY KEY,
IndexableColumn1 INT,
IndexableColumn2 DATETIME,
IndexableColumn3 VARCHAR(10),
UNIQUE (IndexableColumn1,ID),
UNIQUE (IndexableColumn2,ID),
UNIQUE (IndexableColumn3, IndexableColumn2, ID)
)
INSERT INTO @Test (ID, IndexableColumn1, IndexableColumn2, IndexableColumn3)
VALUES
(1,0,GETDATE(),'abc'),
(2,0,'2010/05/25','zzz'),
(3,1,GETDATE(),'zzz')
SELECT t.name, i.name, i.index_id
FROM tempdb.sys.tables t
INNER JOIN tempdb.sys.indexes i ON t.object_id = i.object_id

If the primary key is enforced by the clustered index, this does not make the index any wider than it would be were it defined as a non-unique index with Create Index, as a non-unique non-clustered index always gets the clustering key added to the key columns.

I think that’s this myth suitably busted.

Changes to Table Variables are not logged

A fairly uncommon myth, but I have seen this a time or two, so I thought I’d tackle it as my third.

Table variables don’t participate in transactions, hence nothing is written to the transaction log when changes are made to them.

This again has two parts to it

1. Table variables don’t participate in transactions
2. Operations on table variables are not logged

The first part is completely true. Table variables do not participate in user transactions and they are not affected by an explicit rollback. Easily demonstrated.

DECLARE @TransactionTest TABLE (
ID INT IDENTITY PRIMARY KEY,
SomeCol VARCHAR(20)
)
INSERT INTO @TransactionTest (SomeCol) VALUES ('Row1')
INSERT INTO @TransactionTest (SomeCol) VALUES ('Row2')
BEGIN TRANSACTION
INSERT INTO @TransactionTest (SomeCol) VALUES ('Row3')
ROLLBACK TRANSACTION
SELECT * FROM @TransactionTest

That final select returns 3 rows, not the two that might be expected. The rollback did not affect the table variable.

So does that lack of participation imply that there is no logging? Well, no. My university logic text would call this a Non sequitur fallacy (conclusion does not follow from its premises). The fact that explicit rollbacks don’t affect table variables in no way implies that there’s no logging happening. Let’s have a look into the transaction log to prove it.

USE tempdb -- make sure that the correct database is in use
GO
CHECKPOINT –- To truncate the log and indicate the start of the test
DECLARE @TransactionTest TABLE (
ID INT,
SomeCol VARCHAR(20)
)
SELECT name AS TableVariableActualName FROM tempdb.sys.tables
INSERT INTO @TransactionTest (ID, SomeCol)
VALUES
(0,'Row1'),
(1,'Row2'),
(2,'Row3')
SELECT Operation, AllocUnitName, [Begin Time], [End Time] FROM fn_dblog(NULL, NULL)
GO

The alloc unit name matches the table variable’s name as defined in the system tables, the times for the begin and end transaction matched. I don’t think there’s any arguing that the changes to the table variable are logged.

The next interesting question is whether there’s more or less logging than for a temp table, more or less logging than for a permanent table. Only one way to find out.

I’m going to run exactly the same code with the table variable replaced by a temp table (same structure) and then I’m going to create a new user database and run exactly the same code just using a permanent table.

First the temp table.

And now the permanent table in a user database

From that it appears that the table variable logs less than the temp table which logs less than the user table, however the table variable does still has some logging done.

‘But why?’ I hear people asking. After all, TempDB doesn’t log redo information and, since table variables don’t participate in transactions there’s no need to log undo information. So why log at all?

Because an explicit rollback (ROLLBACK TRANSACTION) is not the only time that changes to a table will have to be undone. Consider this one.

DECLARE @TransactionTest TABLE (
ID INT PRIMARY KEY,
SomeCol VARCHAR(20)
)
INSERT INTO @TransactionTest (ID, SomeCol)
VALUES
(0,'Row1'),
(1,'Row2'),
(1,'Row3')

That third row will fail with a primary key violation. If the table variable didn’t log at all, SQL would have no way of undoing the inserts of the first two rows when the third one fails. That’s not permitted, an insert is an atomic operation, it cannot partially succeed. Hence changes to a table variable must be logged sufficiently to allow SQL to generate the undo operations in cases like this. A glance over the transaction log shows in detail what happened

Two rows inserted, followed by two rows deleted, as SQL generated operations to undo the insert statement, followed by an abort transaction.

I think that’s enough on this. As for the myth that changes to table variables aren’t logged, I believe that’s sufficiently disproven by this point.

In Conclusion

Table Variables are memory-only: False

Table Variables cannot be indexed: False

Changes to Table Variables are not logged: False