Table variable and Table type in transaction

The myth is to roll back the work done inside the transaction, in case of any error during the transaction. However this is not the case with the SQL Server table variable, table type and variables. That is, the changes made on these types are not rolled back even when there is an error.

Let me explore this with an example.

In order to distinguish variable from table, I have created one temporary table and local variable. Inside the transaction, value 1 is assigned to the variable then 256. Assigning of value 256 will cause error as the variable data type is tinyint. The same applied for the temporary table. Due to the error, the transaction enter into the roll back section and the entire transaction rolled back successfully. However by running the select statement “select @num as num” value 1 is return whilst “select * from #tbl” return nothing as expected. This clearly shows that there is no effect for local variable even though the transaction rolled back.

use tempdb
GO
create table #tbl (c1 tinyint)
declare @num tinyint
begin tran
set @num = 1
set @num = 256
insert into #tbl (c1) values(1)
insert into #tbl (c1) values(256)
if @@ERROR =0
commit tran
else
rollback tran
select @num as num
select * from #tbl

The same is applicable for table variable and table type. Sample using table variable:

declare @tbl as table (c1 int)
create table #tbl (c1 int)
begin tran
insert into @tbl (c1) values (1)
insert into @tbl (c1) values (1/0)
insert into #tbl (c1) values(1)
insert into #tbl (c1) values(1/0)
if @@ERROR =0
commit tran
else
rollback tran
select * from @tbl
select * from #tbl

Sample using table type:

create type mytab as table(c1 tinyint)
GO
declare @mytab as mytab
begin tran
insert into @mytab(c1) values (1)
insert into @mytab(c1) values (256)
if @@ERROR = 0
commit tran
else
rollback tran
select * from @mytab