SQL Server Table Variables in Transactions

Today in one of the forum I found an intersting question to execute few SQL Statments as not part of the enclosed the transaction.

Let us consider we have a following Scenario:
We have lots of statements participating in the transaction as

Begin Transaction

SQL Inerts Statement 1
Log a message in Log Table
SQL Update Statement 1
Log a message in Log Table
SQL Delete Statement 1

Commit Transaction / Rollback Transaction

In the above block if the transaction is rollbacked then we will be loosing the enteries in Log table too. But if we need the data available in the Log tables without participating in the transaction...

Now the Question is How we can acheive it...?

Immediately after see the question, most of the people thinks about using inner transaction. we have to remember that:

"Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed."

So the Inner/Nested Transaction are ruled out... Now we have to think that is there any way we can get the solution...?

Yes, we can do that by using Local Table Variables. One of the importatnt behaviour of the Local Table Variable will not have any impact of the transaction. Which means irrespective of the Commit/Rollback there will not be any change in the local table variables.

Following the sample demomstrating the same:

-- Create a Sample Table to test
Create Table tblSample(col1 varchar(10))

-- declare log table variable
Declare @Tbl table(Col1 varchar(10))

--
Begin TRansaction

-- Insert Data into table variable
INSERT INTO @Tbl values ('A11')

-- Insert data into actual table
INSERT INTO tblSample
SELECT * FROM
(
Select 'A' Col1
UNION ALL
Select 'B' Col1
) T

-- Insert data into table variable
INSERT INTO @Tbl values ('B11')

rollback Transaction
--commit Transaction

SELECT * from @Tbl
SELECT * from tblSample

Run the above block once with rollback and second time with commit transaction, you will find the same data in the log table @tbl.

Hope this provides some additonal info on local table variables and how we can use in these scenarios.