What happens to the variable value set during the transaction, if its rollback?

DECLARE @my_age INT = 20;

BEGIN TRAN
SET @my_age += 30;
ROLLBACK;

SELECT @my_age;

Answer:

Even though the transaction is rolled back, the value set to the variable remains same.

Changes to variables aren't affected by the rollback of a transaction.

Reference:

Microsoft docs

Brent Ozar

If you like this SQL interview question, you may also like the below interview question and answers.

• How to do reciprocal in SQL?
• How to generate permutations in SQL?
• How to find account balance (running total) in SQL?
• What happens to the records inserted into a table variable, if the transaction rollback?