Introduction
Table variables are special types of local variables that can be used to store data temporarily, similar in many ways to temporary tables in SQL Server but they are intended for a different purpose. Temporary tables are physical tables stored in the tempdb database and have statistics and indexes, table variables are logical objects that are stored in memory and have no statistics or indexes.
In a SQL Server database, it is beneficial to have a table where we log entries for the different stages or steps throughout a process so we can review row counts, how much time it took to go from one step to the next one, and in case of a failure, it is also useful to know how far the process went before it aborted execution. Therefore, we need a logging mechanism that persists through rollbacks, and that’s where table variables come into place.
Understanding Rollbacks
Rollbacks erase all data modifications made from the start of the transaction or to a savepoint. They also free resources held by the transaction. Rollbacks are triggered implicitly or explicitly but regardless of the method they impact physical and temporary tables where we are storing the data, but they do not have any effect on table variables, we can take advantage of the table variables’ immunity to rollbacks to implement logging that will keep the data intact even after the process fails.
Implementation Steps
It is a good practice to track the progress of an SSIS package or a SQL job or even a stored procedure for the purpose of troubleshooting or to identify how long it takes for each step to complete, compare when things go wrong or how performance has been affected over time.
The first step that I recommend is the creation of a log table, this is going to be a regular SQL server table where we are going to store the logged entries. We are going to flush data to the table only once either when the transaction commits or when there is an error, and a rollback is performed.
To capture the data along the way we are going to use a table variable because table variables are not affected by rollbacks and so if there is a failure the data already in the table variable will be preserved. It would be useful to create a data type and then the table variable will reference the data type instead of an actual table structure, that way we can reuse the code in many different processes without having to list all of the columns and data types of each column every time the table variable is created.
All we have to do at this time is insert a row into the table variable whenever we want to track an event or step or log specific information and then once the process completes successfully transfer the data over from the table variable to the log table (regular physical table). In the catch block of the stored procedure, we should also flush the data from the table variable to the physical log table if a rollback is triggered.
Example Use Case
Here is an example of a Log table:
CREATE TABLE [dbo].[T_LogEventsandSteps]( [T_LogEventsandSteps_ID] [int] IDENTITY(1,1) NOT NULL, [JobName] [varchar](50) NOT NULL, [JobID] [int] NOT NULL, [EventorErrorDescription] [varchar](max) NULL, [NumberofRows] [int] NULL, [EventErrorFlag] [bit] NOT NULL, [EventLogDateTime] [datetime2](7) NOT NULL, PRIMARY KEY CLUSTERED ( [T_LogEventsandSteps_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] ALTER TABLE [dbo].[T_LogEventsandSteps] ADD CONSTRAINT [DF_T_LogEventsandSteps_EventErrorFlag] DEFAULT ((0)) FOR [EventErrorFlag] ALTER TABLE [dbo].[T_LogEventsandSteps] ADD CONSTRAINT [DF_T_LogEventsandSteps_EventLogDateTime] DEFAULT (sysdatetime()) FOR [EventLogDateTime]
Next, we create a User Defined Table Type. This will hold our error information.
CREATE TYPE [dbo].[Events] AS TABLE( [ID] [int] IDENTITY(1,1) NOT NULL, [EventorErrorDescription] [varchar](max) NULL, [EventDateTime] [datetime2](7) NULL DEFAULT (sysdatetime()), [EventErrorFlag] [bit] NOT NULL DEFAULT ((0)), PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) )
We also need a way to transfer data from table variable to log table. We will use a stored procedure to do this:
CREATE PROCEDURE [dbo].[S_LogProcessEvent_FlushData] ( @TableEvents [dbo].[Events] READONLY, @JobName [varchar](50) = NULL, @JobID [int] = NULL, @NumberofRows [int] = NULL ) AS BEGIN SET NOCOUNT ON; INSERT INTO [dbo].[T_LogEventsandSteps] (JobName,JobID,NumberofRows,EventorErrorDescription,EventErrorFlag) SELECT @JobName, @JobID, @NumberofRows, EventorErrorDescription, EventErrorFlag FROM @TableEvents ORDER BY ID; END
Here is a sample stored procedure that uses table variable functionality for logging entries:
CREATE PROCEDURE [dbo].[SI_ProcessSample1]
AS
BEGIN
DECLARE @ProcessName varchar(max) = 'Test Table Variable';
DECLARE @EventDescription varchar(max);
DECLARE @NumberOfRecords int = 0;
DECLARE @RunID int = COALESCE((SELECT MAX(JobID)+1 FROM [dbo].[T_LogEventsandSteps] WITH(NOLOCK) WHERE JobName=@ProcessName),1)
DECLARE @TableEvents [dbo].[Events];
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET XACT_ABORT ON;
BEGIN TRY
SET @EventDescription = 'Begin transaction'
BEGIN TRANSACTION
SET @EventDescription = 'Test processsing: Begin'
INSERT INTO @TableEvents(EventorErrorDescription)
VALUES(@EventDescription);
SET @EventDescription = 'Getting new data to load to staging table'
INSERT INTO @TableEvents(EventorErrorDescription)
VALUES(@EventDescription);
--Add here stored procedure or queries to load data to staging table
SET @EventDescription = 'Pulling payments'
INSERT INTO @TableEvents(EventorErrorDescription)
VALUES(@EventDescription);
--Add here stored procedure or queries to pull payments
SET @EventDescription = 'Test processsing: End'
INSERT INTO @TableEvents(EventorErrorDescription)
VALUES(@EventDescription);
SET @EventDescription = 'Commit transaction'
COMMIT TRANSACTION
--Set @NumberofRecords here, example below:
SET @NumberOfRecords=100;
EXEC [dbo].[S_LogProcessEvent_FlushData] @TableEvents,@ProcessName,@RunID,@NumberOfRecords; --Flush data to disk.
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SET @EventDescription = CONCAT('ERROR IN ' , '[dbo].[SI_ProcessSample1]: ',COALESCE(ERROR_MESSAGE(),'')); --More information about the error can be added here, for example: Error Line Number, Error Severity, Error State, etc.
INSERT INTO @TableEvents(EventorErrorDescription,EventErrorFlag)
VALUES(@EventDescription,1);
EXEC [dbo].[S_LogProcessEvent_FlushData] @TableEvents,@ProcessName,@RunID,@NumberOfRecords; --Flush data to disk.
THROW; --Rethrow error
END CATCH
END
Conclusion
In this article we explained how utilizing SQL table variables for logging entries provides a robust solution to ensure data persistence even in the event of rollbacks, we briefly described the impact of rollbacks in the data and provided step-by-step instructions on how to declare and use table variables for logging, including relevant SQL code with an example use case.
References:
The Table Variable in SQL Server (sqlshack.com)