Rollback transaction behavior on table variable

Table variable is a special kind of data type which stores the result set and returns data for processing. Table variable can be used in store procedures and functions. Table variables have limited scope and data will be stored in temporary storage. So they are not affected with transaction rollbacks.

In the below script we will see practically how rollback behaves for table variable.

DECLARE @student AS Table (id INT,Name VARCHAR(50) NOT NULL)
 BEGIN TRY
    BEGIN TRANSACTION       
    INSERT INTO @student Values(1,'Ram')
    INSERT INTO @student Values(2,'Rahim')
    INSERT INTO @student Values(3,NULL)
    INSERT INTO @student Values(4,'Rohan')
    COMMIT TRANSACTION
    SELECT *,Name FROM @student -- will not return data due to error at id=3
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    SELECT *,id FROM @student
END CATCH

Output:

In summary, we can say that Rollback Transaction does not work for table variables.

Note: Temporary tables supports Rollback Transaction. This is one of difference between Table variable and Temporary tables.

Inorder to test with Temporary tables repalce first t-sql statement with "CREATE TABLE #student(id INT,Name VARCHAR(50) NOT NULL)" and table name as #student in above script. Output will be nothing, because Rollback Transaction supports Temporary tables.