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?
|
1 2 3 4 5 6 7 |
DECLARE @MySalary INT = 100000; BEGIN TRAN SET @MySalary = @MySalary * 2; ROLLBACK; SELECT @MySalary; |
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 is 200,000.
What about table variables?
When I run this query, how many rows will the SELECT return?
|
1 2 3 4 5 6 7 8 9 |
DECLARE @People TABLE (Name VARCHAR(50)); BEGIN TRAN INSERT INTO @People VALUES ('Bill Gates'); INSERT INTO @People VALUES ('Melinda Gates'); INSERT INTO @People VALUES ('Satya Nadella'); ROLLBACK SELECT * FROM @People; |
I polled y’all on Twitter, and here were the results:
How many rows will I get?
DECLARE @People TABLE (Name VARCHAR(50));
BEGIN TRAN
INSERT INTO @People VALUES ('Bill Gates');
INSERT INTO @People VALUES ('Melinda Gates');
INSERT INTO @People VALUES ('Satya Nadella');ROLLBACK
SELECT * FROM @People;
— Brent Ozar (@BrentO) August 19, 2020
Even less of you knew that the correct answer is 3 rows:
Variables ignore transactions.
I can understand why so many of y’all don’t know because none of this is on the variables documentation page. I’m kidding, of course. We both know you don’t read the documentation, either.
This is why I teach training classes. You should probably take one.
32 Comments. Leave new
I’m curious, why would someone answer “1 row”?
We once interviewed a candidate who answered “3” when asked how many clustered indexes you could have on a table. I’m pretty sure it’s the same guy.
Recently, I was assisting a previous company interview my replacement (because Im super helpful like that). Not one person of the first 10 could answered “how many clustered indexes can a table have?” correctly.
Wow! Just wow! I mean… Why would someone even apply for a SQL Server job if you don’t know that…
That is also my question. I get those 59% but who ever on that 2.4% should be a student who just start to learn SQL
That person thinks like sql server engine
and that’s why I like your Post so 😀
Sometimes I use this to bring some data (debug, diagnostic) out from transaction. In debug, development or troubleshooting, NOT in normal production.
Should I be happy to be in the majority?
While this was not covered in class – that is no excuse.
Must try this with ORACLE (no, 1 row is not the answer)
This is a programming 101 type question and an excellent example of why people should at least study a second or third unrelated language (if not learn how to write a bit of it) before being allowed to write anything lol.
And to the part about variables ignoring transactions – they don’t so much ignore transactions, but aren’t a part of the transaction, they only exist in memory.
Not part of the transaction? The @Variable only lives during the transaction.
The next Go statement or end of file terminates it.
Part of the transaction space or package but is not committed.
rollback is the end of the transaction – go would be the end of the batch wouldn’t it?
Keith, I agree with you But which language?
Pascal – Live VW bug. Strong durable – not fancy and loved by intellectuals.
Assembler – Kit Car many statements and everything must be defined and understood. Manual will not help much
LISP – More like LSD. Only two statements but you can do anything
K&R C – Cross between a Maserati bora (no breaks) and TESLA car. Fastest way to get the impossible done even if that is killing yourself.
ADA – Army Jeep. one size fits all.
I actually don’t know. in college and the early part of my career I was exposed to a bunch of different languages, was pretty bad at them early on but at least understood how different types of language worked and common constructs between them.
My theory is: Java or C# first, C or C++ second, a scripting language (but for the love of god not python) I really hate to say php or perl for relatively unuseful they are now, but I think they are a better learning tool than javascript. I do think an assembler class would be useful if for nothing else, to be able to intelligently participate in a conversation bashing the absurdity of nullable booleans. Don’t need to learn any of them to ever be able to write something useful in them, but at least be able to write educational type functions or apps, forget how when class is over and then keep the knowledge of the basic constructs of that language.
Brent – Does this mean @variables are not logged ?
Work is done outside the ACID model ?
Oh, that is a good question. If I create a table variable, and I load data into it and it gets so big that it has to store the data in tempdb, does it do it without using any log space in tempdb. Then any insert, updates or deletes on that table variable would also not use log space in tempdb, only data space would be used.
That question actually melts my mind a bit.
I have a few thoughts that are speculative in nature, but would be curious to see what brent says
My general thought on stuff like that is to point folks to the second best thing Paul Randal taught me: https://ozar.me/2017/01/the-two-best-things-paul-randal-taught-me/
@Keith, @Stephen
Brent has spoken Paul’s words (recycle – reuse – sometimes re learn)
1) I do not know [no pain there]
2″When you ask someone a question, you’re making a demand on their time. Before you take their time from them, do them a favor – spend a few moments building a test.”
— this is where I’m left unsupervised with SQL server to do a test —
“Hold my beer – now watch this. I’m gonna try something. Performance may suck as there are no statistics on the table … but…. “
It’s trivial to prove the variable in the example in this blog is not a part of the transaction.
You do this:
USE [tempdb];
DECLARE @TEST int = 1;
DBCC OPENTRAN;
BEGIN TRAN
USE [TEMPDB];
SELECT @TEST = @TEST * 5;
CREATE TABLE [#A] ([A] int);
INSERT INTO [#A] VALUES (@TEST);
DBCC OPENTRAN;
ROLLBACK;
USE [TEMPDB];
DBCC OPENTRAN;
And you get back one transaction.
You do this:
USE [tempdb];
DECLARE @TEST int = 1;
DBCC OPENTRAN;
BEGIN TRAN
USE [TEMPDB];
SELECT @TEST = @TEST * 5
SELECT @TEST;
DBCC OPENTRAN;
ROLLBACK;
USE [TEMPDB];
DBCC OPENTRAN;
and you get back zero transactions. The other two opentrans are just to prove the transaction isn’t somewhere else. The SELECT @TEST; in the second example is to prove the query optimizer isn’t just optimizing it out.
My theory on a table variable spilling to tempdb is that there is a logged transaction, but the transaction isn’t from the writes to table, but the spill. I don’t know how you would test whether the transaction you are seeing is from the writes to the the table or the spill that is effectively equivalent to the write to memory in the example in this post.
from the opentran output in this example, you get a transaction name of ‘user_transaction’. I wondered if a spill would say something like ‘system_transaction’ or ‘server_transaction’ but then I set my desktop sql instance to 100 Mb of ram instead of 1000 mb, can’t get into it, and my break is now over.
@Keith – thank you. I did not know about DBCC OPENTRAN;
This would make a good presentation at SQL Saturday (hint hit, nudge nudge, wink wink)
On a related note…. if variables are updated by select statements, and the select statement isn’t a sub-query and returns 0 rows, then the select assignment doesn’t happen.
Code below is bit obvious with where 1 = 2 so imagine a more complicated scenario that could see cursor fans run code against the same record twice.
DECLARE @x NVARCHAR(25) = ‘Initial value’
SELECT @x AS ‘@x at stage 1’
SELECT @x = ‘Value1’ WHERE 0=1
SELECT @x AS ‘@x at stage 2’
SELECT @x = ‘Value2’ WHERE 1=1
SELECT @x AS ‘@x at stage 3’
Solution: make the select a sub-query (or use set but set needs a sub-query anyway).
Hola Amigo! I didn’t know … and? When writing the code you see that it does not work, you change it and that’s it. You write it down on the list of curious things but, since it’s silly, you end up forgetting it. And nothing happens.
Ah, so you always see when code doesn’t work, eh? Wow. That’s impressive! You must be one of those 10X Developers that I’ve heard so much about. 10X for stopping by!
Well, not always. But if you are not sure of something you always try. Following your teachings! When in doubt, try. SQL Server does not bite!
Lucas – Absolutely . Ninja train, *practice* and learn new stuff. So must we.
wOW, the hubris in this thread is palpable. In the reading I’ve done over the past few months, I’ve noticed that SQL-heads like to flip-flop back and forth between presenting an inclusive, mentoring vibe, and a beat-down on folks who don’t know as much as they do. Pick a lane.
Good point. We should all always just have exactly one approach to life, and never deviate from that one specific emotion and approach. It’ll be perfect for all situations. I never thought of that before! You should write a book.
I think you can tell which lane I picked this morning. 😉
Great post, thanks for sharing! Good news, it looks like this behavior is now officially documented.
“Changes to variables aren’t affected by the rollback of a transaction.”
I remember first learning about this a while back with table variables. Such an interesting “gotcha” question. I’m sure many people would appreciate this behavior with that @MySalary variable. 😉
Yeah, looks like Steve Jones checked in a change: https://github.com/MicrosoftDocs/sql-docs/commits/live/docs/t-sql/language-elements/variables-transact-sql.md
? i thought this would be common knowledge. I guess not. #temp tables do participate in transactions. I try to be very careful about where I load a temp table (before the begin transactions when possible), otherwise you can have some nasty rollback.