Half Of You Don’t Understand Variables and Transactions.


Do table variables and variables respect transactions? If I set the value of a variable during a transaction, and I roll it back, what happens?

DECLARE @MySalary INT = 100000; BEGIN TRAN SET @MySalary = @MySalary * 2; ROLLBACK; SELECT @MySalary;

I polled y’all on Twitter:

What will be the result of this query?
DECLARE @MySalary INT = 100000;

BEGIN TRAN
SET @MySalary = @MySalary * 2;
ROLLBACK;

SELECT @MySalary;

— Brent Ozar (@BrentO) August 19, 2020

I can’t bring myself to make a joke about the popular vote, so let’s move on and just point out that the correct answer