As a SQL practitioner, you might have wondered whether there's ever a good reason to use table variables instead of temporary tables. One of the benefits of table variables is that, like normal variables, a rollback of a user transaction doesn't affect them. If a single statement doesn't complete, its changes are rolled back. But once a single statement completes, a rollback of the user transaction—whether intentional or not—doesn't undo the changes to the table variable. Conversely, temporary tables are affected by a rollback just like normal tables are. Any changes that occur to a temporary table within a user transaction are undone if the transaction is rolled back.
This characteristic of table variables can be especially useful in error handling with transactions and triggers. Suppose that when an error occurs in a transaction or a trigger, or when some condition requires you to roll back the transaction, you need to save to a log table the data that was generated by the transaction.
If you first try to copy the data to the log table and then roll back the transaction, you undo the work that wrote to the log table. Furthermore, if you use TRY-CATCH to handle errors and your transaction enters a doomed state, you aren't allowed to make any writes to the user tables. If you issue a rollback and then try to copy the data to the log table, it's too late because the rollback undoes the changes that were created by the transaction.
This might seem like a catch-22 situation. The solution is to save the data to a table variable before you issue the rollback, then issue the rollback (which doesn't affect the table variable, unlike with temporary tables), and then in a new transaction copy the data from the table variable to the log table.
I'll demonstrate this technique with two examples: one with a doomed transaction and the other with a trigger.
Example with a Doomed Transaction
Run the code in Listing 1 to create a table called T1 and a sequence called SeqT1batch.
SET NOCOUNT ON; USE tempdb; IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL DROP TABLE dbo.T1; IF OBJECT_ID(N'dbo.SeqT1batch', N'SO') IS NOT NULL DROP SEQUENCE dbo.SeqT1batch; CREATE SEQUENCE dbo.SeqT1batch AS INT MINVALUE 1 CYCLE CACHE 10000; CREATE TABLE dbo.T1 ( batch INT NOT NULL, step INT NOT NULL, data FLOAT NOT NULL, CONSTRAINT PK_T1 PRIMARY KEY(batch, step) ); GO
Now, examine the code in Listing 2.
BEGIN TRAN;
DECLARE
@batch AS INT = NEXT VALUE FOR dbo.SeqT1batch,
@i AS INT = 1;
WHILE @i <= 5
BEGIN
INSERT INTO dbo.T1(batch, step, data)
VALUES(@batch, @i, LOG(ABS(CHECKSUM(NEWID()))%10));
SET @i += 1;
END;
COMMIT TRAN;
GO
This code represents a process that runs as a single transaction. The process creates a new batch number using the sequence object and then adds five rows to T1 with the batch number, the step number, and the result of some computation.
I'm using a LOG function just as an illustration for code that can fail. In the case of LOG, it fails when you pass a nonpositive input.
I use randomization to generate a value in the range 0 through 9, so 1 in every 10 computations will cause a failure on average. Because the code issues 5 inserts with the computation, 1 in every 2 executions of the code will fail on average.
This type of error happens to be a transaction-aborting error; therefore, as soon as it occurs, the transaction is rolled back and all changes are undone. Try running the code a few times until you get an error such as the following:
Msg 3623, Level 16, State 1, Line 40 An invalid floating point operation occurred.
Suppose that you need to add logic that in case of an error you save the rows that were generated in the current batch to a log table before you roll back the transaction. Use the code in Listing 3 to create the log table.
IF OBJECT_ID(N'dbo.T1Log', N'U') IS NOT NULL DROP TABLE dbo.T1Log;
CREATE TABLE dbo.T1Log
(
lsn INT NOT NULL IDENTITY
CONSTRAINT PK_T1Log PRIMARY KEY,
ts DATETIME2(0) NOT NULL DEFAULT(SYSDATETIME()),
batch INT NOT NULL,
step INT NOT NULL,
data FLOAT NOT NULL
);
GO
Listing 4 demonstrates how you might want to implement the solution using a TRY-CATCH construct and a table variable.
BEGIN TRY
BEGIN TRAN;
DECLARE
@batch AS INT = NEXT VALUE FOR dbo.SeqT1batch,
@i AS INT = 1;
WHILE @i <= 5
BEGIN
INSERT INTO dbo.T1(batch, step, data)
VALUES(@batch, @i, LOG(ABS(CHECKSUM(NEWID()))%10));
SET @i += 1;
END;
COMMIT TRAN;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
PRINT
'Error occurred. Saving partially generated data for batch '
+ CAST(@batch AS VARCHAR(36)) + ' to T1Log.';
-- copy data from T1 into table variable
DECLARE @T1Temp AS TABLE
(
batch INT NOT NULL,
step INT NOT NULL,
data FLOAT NOT NULL
);
INSERT INTO @T1Temp(batch, step, data)
SELECT batch, step, data
FROM dbo.T1
WHERE batch = @batch;
-- roll back transaction
ROLLBACK TRAN;
-- copy data from table variable into log table
INSERT INTO dbo.T1Log(batch, step, data)
SELECT batch, step, data
FROM @T1Temp
WHERE batch = @batch;
END
ELSE
PRINT
'Error occurred. Transaction terminated. Cannot save data for batch '
+ CAST(@batch AS VARCHAR(36)) + ' to T1Log.';
END CATCH;
GO
When a dooming error occurs in a transaction within a TRY block, the transaction enters a failed, or doomed, state and control is passed to the CATCH block. Under a doomed state, you're not allowed to make changes to a user table, and eventually you'll have to roll back the transaction.
The good news is that you're allowed to write data to a table variable. So in the CATCH block the code declares a table variable, copies the rows of the current batch that did make it to T1 to the table variable, rolls back the transaction, and then copies the rows from the table variable to the log table.
Run this code a few times until you get a message saying that there was an error. Then query the log table by running the following code:
SELECT * FROM dbo.T1Log;
When I ran this code after three failures, I got the output that Figure 1 shows.
lsn ts batch step data ---- -------------------- ------ ----- ------------------ 1 2014-12-02 07:29:09 10 1 0.693147180559945 2 2014-12-02 07:29:09 10 2 2.19722457733622 3 2014-12-02 07:29:09 10 3 1.94591014905531 4 2014-12-02 07:29:09 10 4 2.19722457733622 5 2014-12-02 07:29:12 11 1 1.09861228866811 6 2014-12-02 07:29:13 12 1 1.79175946922805 7 2014-12-02 07:29:16 15 1 2.07944154167984 8 2014-12-02 07:29:16 15 2 0 9 2014-12-02 07:29:16 15 3 1.79175946922805
Example with a Trigger
Another case in which table variables can come in handy is with triggers. Suppose that in a certain condition you need to issue a rollback in a trigger, but you need to save the data from the inserted and/or deleted tables in log tables. If you first copy the data from the tables inserted and deleted to the log tables and then issue a rollback, the write to the log tables is undone. If you issue a rollback before copying the data, the inserted and deleted tables are emptied. The solution is to copy the data from the tables inserted and deleted to table variables, then issue a rollback, and then copy the data from the table variables to the log tables.
As an example, run the code in Listing 5 to create a table called T2 and an AFTER INSERT, UPDATE trigger on the table.
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID(N'dbo.T2', N'U') IS NOT NULL DROP TABLE dbo.T2;
CREATE TABLE dbo.T2
(
keycol INT NOT NULL IDENTITY
CONSTRAINT PK_T2 PRIMARY KEY,
data INT NOT NULL
);
GO
CREATE TRIGGER trg_T2_insert ON dbo.T2 AFTER INSERT, UPDATE
AS
PRINT 'Trigger trg_T2_insert at work...';
GO
Currently the trigger has just a PRINT statement saying that it's at work, but normally you would have some more meaningful work there.
Suppose that when a user inserts or updates rows in T2 with a value of 0 in the data column in any of the rows, you're supposed to roll back the transaction. Why not use a simple CHECK constraint? Suppose you need to write to a log table the rows that the user attempted to insert or update, along with the action type (INSERT or UPDATE) and a batch number you obtain from a sequence object that represents the failed statement.
To achieve this, you'll need to create the log table, create a sequence object to number the failed statements, and alter the trigger with the new logic. Run the code in Listing 6 to create the sequence object SeqT2FailedStmt and the log table T2Log.
IF OBJECT_ID(N'dbo.T2Log', N'U') IS NOT NULL DROP TABLE dbo.T2Log;
IF OBJECT_ID(N'dbo.SeqT2FailedStmt', N'SO') IS NOT NULL
DROP SEQUENCE dbo.SeqT2FailedStmt;
CREATE SEQUENCE dbo.SeqT2FailedStmt AS INT
MINVALUE 1 CYCLE CACHE 10000;
CREATE TABLE dbo.T2Log
(
lsn INT NOT NULL IDENTITY
CONSTRAINT PK_T2Log PRIMARY KEY,
ts DATETIME2(0) NOT NULL DEFAULT(SYSDATETIME()),
failedstmt INT NOT NULL,
theaction VARCHAR(6) NOT NULL,
keycol INT NOT NULL,
data INT NOT NULL
);
GO
Run the code in Listing 7 to alter the trigger, adding the new logic.
ALTER TRIGGER trg_T2_insert ON dbo.T2 AFTER INSERT, UPDATE
AS
PRINT 'Trigger trg_T2_insert at work...';
IF EXISTS(SELECT * FROM inserted WHERE data = 0)
BEGIN
PRINT 'Rolling back transaction. Data can be found in T2Log.';
DECLARE
@theaction AS VARCHAR(6) =
CASE WHEN EXISTS(SELECT * FROM deleted) THEN 'UPDATE' ELSE 'INSERT' END,
@failedstmt AS INT = NEXT VALUE FOR dbo.SeqT2FailedStmt;
-- copy data from the table inserted into table variable
DECLARE @T2Temp AS TABLE
(
keycol INT NOT NULL,
data INT NOT NULL
);
INSERT INTO @T2Temp(keycol, data)
SELECT keycol, data FROM inserted;
-- roll back the transaction
ROLLBACK TRAN;
-- copy data from table variable into log table
INSERT INTO dbo.T2Log(keycol, failedstmt, theaction, data)
SELECT keycol, @failedstmt, @theaction, data FROM @T2Temp;
END;
GO
The trigger uses an EXISTS predicate to check if any row with data = 0 exists in the table inserted. If so, it needs to log the rows from inserted in the table T2Log. The code determines whether the action was UPDATE or INSERT based on whether rows exist in the table deleted. It also obtains a new sequence value from the sequence SeqT2FailedStmt to represent the failed statement. The code then declares a table variable, copies the rows from the table inserted to the table variable, rolls back the transaction, and then copies the rows from the table variable to T2Log.
After you alter the trigger, run the following code to insert and update rows in T2:
INSERT INTO dbo.T2(data) VALUES(10),(20),(30); GO INSERT INTO dbo.T2(data) VALUES(0),(5),(15); GO INSERT INTO dbo.T2(data) VALUES(40),(50),(60); GO INSERT INTO dbo.T2(data) VALUES(25),(0),(35); GO INSERT INTO dbo.T2(data) VALUES(70),(80),(90); GO INSERT INTO dbo.T2(data) VALUES(45),(55),(0); GO UPDATE dbo.T2 SET data = data - 50 WHERE data >= 50; GO
The trigger will roll back some of the modifications. When it does, you'll get the following error message:
Trigger trg_T2_insert at work... Rolling back transaction. Data can be found in T2Log. Msg 3609, Level 16, State 1, Line 227 The transaction ended in the trigger. The batch has been aborted.
Query the table T2Log to see that the trigger managed to save the rows despite the rollback:
SELECT * FROM dbo.T2Log;
I got the output that Figure 2 shows.
lsn ts failedstmt theaction keycol data ----- -------------------- ----------- --------- ------- ----- 1 2014-12-02 07:37:42 1 INSERT 6 15 2 2014-12-02 07:37:42 1 INSERT 5 5 3 2014-12-02 07:37:42 1 INSERT 4 0 4 2014-12-02 07:37:42 2 INSERT 12 35 5 2014-12-02 07:37:42 2 INSERT 11 0 6 2014-12-02 07:37:42 2 INSERT 10 25 7 2014-12-02 07:37:42 3 INSERT 18 0 8 2014-12-02 07:37:42 3 INSERT 17 55 9 2014-12-02 07:37:42 3 INSERT 16 45 10 2014-12-02 07:37:42 4 UPDATE 15 40 11 2014-12-02 07:37:42 4 UPDATE 14 30 12 2014-12-02 07:37:42 4 UPDATE 13 20 13 2014-12-02 07:37:42 4 UPDATE 9 10 14 2014-12-02 07:37:42 4 UPDATE 8 0
Handy Table Variable Behavior
As it turns out, it's quite handy that table variables aren't affected by a user transaction rollback. This behavior is especially useful when you need to save data in a transaction that you need to roll back or in a transaction that has to roll back due to a dooming error. In my next article, I'll cover additional tips concerning table variables.
