Rollback Transaction for table variable in sql server

Table variable is special kind of data type which will store result set and the data will returned 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 roll backs.

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.