Table Variables and User Transactions

Transactionless?  Really?  Hmm. Lets check to be sure. 

Whilst geeking with table variables, waiting for turkey dinner to make its way to the dinner table,  I happened upon a blog or two about table variables being ‘transactionless’.  This sparked my interest because with DML in SQL Server (aside from SELECT) nothing is transactionless.  Meaning that, nothing but nothing avoids the transaction log, period and case closed.  At least that’s how I’ve known SQL Server to operate.  After all that’s the key component of how SQL Server maintains transactional consistency. Without the log capturing changes, the database will quickly become out of whack, and transactional consistency is everything. Ever wonder why restoring tables went away in SQL Server 6.5? Because of this consistency; they couldn’t guarantee it so they removed the feature.

Anyway, this is an interesting feature of table variables.  Run the following:

DECLARE @tbl table (id int)

BEGIN

       BEGIN TRAN

              INSERT INTO @tbl VALUES (1)

              SELECT * FROM @tbl

       ROLLBACK

END   

SELECT * FROM @tbl

GO

Item 1.  Rollback behavior with table variable

CREATE TABLE #Y (ID INT)

BEGIN

       BEGIN TRAN

              INSERT INTO #Y VALUES (1)

              SELECT * FROM #Y

       ROLLBACK

END

SELECT * FROM #Y

GO

Item 2. Rollback behavior with temporary table

You will see that a rollback does nothing for the table variable. Very nice! Essentially, during the batch, SQL Server does not maintain consistency by using the transaction log for a table variable, and the rollback does not happen.  So this means that it is transactionless, right?  Uh, don’t think so.

Run the following with SQL Server profiler on in the background.  I’ve limited this to about 35 million rows so that we can flip back and forth and run a few commands while the script is running. It runs on my laptop in about 30 seconds, give or take.  I run the first script, pause profiler, and then restart it and run the second script. The output is below.

DECLARE @TBL table

       (ID decimal (28,2)

       ,Col varchar (200)

       )

BEGIN TRANSACTION A

       INSERT INTO @TBL

       SELECT x.x, 'a'

       FROM

              (SELECT TOP 35000000 ROW_NUMBER() OVER (ORDER BY a.ID) as x, 'a' as Col

              FROM sys.syscolumns as a

                     ,sys.syscolumns as b

                     ,sys.syscolumns as c

              ORDER BY 1

              )x

       SELECT COUNT(1) FROM @TBL

ROLLBACK TRANSACTION A

SELECT COUNT(1) FROM @TBL 

GO

Item 3.  Expanded table variable transaction

DROP TABLE #X

CREATE TABLE #X

       (ID decimal (28,2)

       ,Col varchar (200)

       )

BEGIN TRANSACTION A

       INSERT INTO #X

       SELECT x.x, 'a'

       FROM

              (SELECT TOP 35000000 ROW_NUMBER() OVER (ORDER BY a.ID) as x, 'a' as Col

              FROM sys.syscolumns as a

                     ,sys.syscolumns as b

                     ,sys.syscolumns as c

              ORDER BY 1

              )x

       SELECT COUNT(1) FROM #X

ROLLBACK TRANSACTION A

       SELECT COUNT(1) FROM #X   

go

Item 4.  Expanded temporary table transaction

Figure 1.  Item 3 execution in SQL Profiler

Notice that I name my transaction so I can identify ObjectName.  First off, a transaction has begun, so the notion of transactionless is not the case. Clearly the BEGIN and ROLLBACK have occurred.  Moreover, a view of DBCC Loginfo reveals that plenty is happening inside of the transaction log when the script is running. A screenshot of DBCC Loginfo below, but if you like, while item 3 is running, run this DBCC command to see that the log is in fact recording the changes and is creating in FSeqNos and new LSNs.

Figure 2. DBCC Loginfo when running Item 3

Now let’s run the Item 4 and capture its results in SQL Profiler:

Now the question is – what are some of the things we see in Figure 1 output  (table variable) and Figure 3 output (temp table)?  One big difference that I see is that a certain type of page allocation, AllocHeapPageSimpleeXactBulk, is issued. We have to assume at this point that this type operation takes some shortcuts; while the data is being recorded in the log, is must be of a minimal type, hence “Bulk” and maybe similar to other types of bulk operations.  This may or may not be significant, but one thing is for certain:  when the batch insert for a table variable completes, all of the previously active LSNs in the log go away the instant that the script completes, while the log stays active for temporary tables. (Run DBCC Loginfo after each script completes to see that the log completely flushes upon the end of the table variable script but does not at the end of the temporary table script). 

In conclusion, first off, table variables are not in the least ‘transactionless’.  DML is captured by the log, regardless of where it is sourced.  This is a key component of a relational database, we already know this.  If it did not exist we would have a transactionally inconsistent state almost immediately. Hence, if the plug was pulled on the server during a transaction, or communications was broken, half-complete transactions would exist throughout.  Second, I come to only a high-level conclusion on the interworkings of SQL Server when both of my scripts are running. Clearly in profiler something different is happening behind the scenes- while transactions are occurring in both, the behavior of table variables vs. temp tables is not at all the same with regards to how each uses the transaction log records, and what types of allocations and transactions are occurring. For table variables, although the log captures changes from DML, they appear to disregard them and explicit TSQL transaction commands are ignored.

By the way, the table variable script executed in 63 seconds, while the same script using temporary tables ran in 34 seconds.  LOL

Lee

--------------------------------

I can see this guy doesn't care for this blog!