Introduction
In our exploration of Temporary Tables and Table Variables in SQL Server, we delved into a detailed examination of these constructs, illustrating their usage, distinctions, and optimal application through examples. Within that discussion, a comparative analysis regarding transactional behavior: temporary tables support transactions, facilitating rollback operations, whereas table variables remain non-participatory in transactions.
In this short blog, we will further illustrate these concepts with a practical example.
Table Variables and Temporal Tables Engage in Transactions
No, table variables do not engage in transactions and lack support for DDL statements such as ALTER, CREATE, and DROP. Conversely, temporary tables support DDL statements and actively participate in transactions.
Example for Table Variables
--Declaring table variable
DECLARE @TableVaribale TABLE(Id INT, Name VARCHAR(100))
--Starting the transaction
BEGIN TRAN
--Inserting a record into table variable
INSERT INTO @TableVaribale
VALUES (1, 'Do Table Variables and Temporal Tables Engage in Transactions?')
--Rollback the transaction
ROLLBACK TRAN
--Selecting the record from table variable
SELECT * FROM @TableVaribale
Result
![Example for Table Variables]()
Example of a Temporary Table
--Creating the temporary table
DROP TABLE IF EXISTS #TemporaryTable
CREATE TABLE #TemporaryTable (Id INT, Name VARCHAR(100))
--Starting the transaction
BEGIN TRAN
--Inserting a record into temporary table
INSERT INTO #TemporaryTable
VALUES (1, 'Do Table Variables and Temporal Tables Engage in Transactions?')
--Rollback the transaction
ROLLBACK TRAN
--Selecting the record from table variable
SELECT * FROM #TemporaryTable
Result
![Example of a Temporary Table]()
Conclusion
While table variables do not participate in transactions and lack support for DDL statements, temporary tables offer robust transactional support and allow for the execution of DDL statements.