April 2, 2010 at 8:37 am
Steve Jones - Editor (4/2/2010)
I think the statement that table variables don't participate in transactions is clearer. It implies that they can be exempt from a rollback, which is handy. I worry about marking them as "not logging" when that could imply something else to less experienced developers.
I like this way of stating it too for the reasons Steve has stated.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 8:54 am
Saying "they don't participate in transactions" could be misleading. If you insert into a temp or permanent table from a table variable (either by itself or as part of a more complex query), there will be transactions, and they can be rolled back. That's not what's meant, but "participate" is a vague enough word to imply something else.
Another difference is that table variables aren't subject to truncate commands.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2010 at 9:03 am
GSquared (4/2/2010)
Saying "they don't participate in transactions" could be misleading. If you insert into a temp or permanent table from a table variable (either by itself or as part of a more complex query), there will be transactions, and they can be rolled back. That's not what's meant, but "participate" is a vague enough word to imply something else.Another difference is that table variables aren't subject to truncate commands.
Two pieces from BOL:
Transactions involving table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources.
Also, because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.
April 2, 2010 at 9:18 am
GSquared (4/2/2010)
Saying "they don't participate in transactions" could be misleading. If you insert into a temp or permanent table from a table variable (either by itself or as part of a more complex query), there will be transactions, and they can be rolled back.
There will be transactions, they can be rolled back, but changes made to the table variable won't be rolled back. Is there a better word than 'participate' to convey that?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2010 at 9:22 am
GilaMonster (4/2/2010)
GSquared (4/2/2010)
Saying "they don't participate in transactions" could be misleading. If you insert into a temp or permanent table from a table variable (either by itself or as part of a more complex query), there will be transactions, and they can be rolled back.There will be transactions, they can be rolled back, but changes made to the table variable won't be rolled back. Is there a better word than 'participate' to convey that?
I'd be specific. "Transactions in table variables can't be rolled back."
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2010 at 9:22 am
not "involved" in transactions maybe? I like participate better.
April 2, 2010 at 9:30 am
GSquared (4/2/2010)
I'd be specific. "Transactions in table variables can't be rolled back."
I dunno, that could give the impression that failed inserts/updates don't roll back (insert 10 rows, row 8 fails, there will be 7 rows in the table variable afterwards)
'Table variables are not affected by explicit transaction rollbacks'?
English can be so hard to get right sometimes...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2010 at 9:30 am
GilaMonster (4/2/2010)
GSquared (4/2/2010)
Saying "they don't participate in transactions" could be misleading. If you insert into a temp or permanent table from a table variable (either by itself or as part of a more complex query), there will be transactions, and they can be rolled back.There will be transactions, they can be rolled back, but changes made to the table variable won't be rolled back. Is there a better word than 'participate' to convey that?
How about:
Also, because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.
April 2, 2010 at 9:33 am
Lynn Pettis (4/2/2010)
GSquared (4/2/2010)
Saying "they don't participate in transactions" could be misleading. If you insert into a temp or permanent table from a table variable (either by itself or as part of a more complex query), there will be transactions, and they can be rolled back. That's not what's meant, but "participate" is a vague enough word to imply something else.Another difference is that table variables aren't subject to truncate commands.
Two pieces from BOL:
Transactions involving table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources.
Also, because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.
Try this:
SET NOCOUNT ON;
DECLARE @T TABLE (
ID INT PRIMARY KEY);
BEGIN TRANSACTION;
INSERT INTO @T (ID) VALUES (1),(2);
SELECT * FROM @T;
SELECT * INTO #T
FROM @T;
SELECT * FROM #T;
ROLLBACK;
SELECT * FROM @T;
SELECT * FROM #T;
You'll get the results of the select from @T both inside and outside the transaction. You'll get the select from #T inside the transaction, but after the rollback, you'll get an error because #T isn't a valid object. It was created with a Select Into that was rolled back, even though the Select Into was from a table variable. You'll get similar results if you explicitly create #T and then change it to Insert Select instead of Select Into, but you won't get an error, you'll get an empty dataset.
That's what I mean by a table variable can "participate" in a transaction. It's not itself subject to the rollback, but a transaction that involved a table variable was rolled back.
It's just the vagueness of the word "participate" that I don't like here. It's meant to say, "a transaction inside a table variable isn't subject to Commit and Rollback commands in the normal sense", but "participate" could also mean "a transaction that involves a table variable", in this context.
That's why I prefer more explicit language in this case. Saying, "you can't roll back a transaction in a table variable" is, to me, more exact, less subject to English's frequent misintrepretation.
Avoiding, "Table variables don't participate in transactions" will make it sound less scholarly/formal, but will, in my opinion, be more clear to someone who isn't familiar with transaction scopes. Someone who is already familiar with transaction scoping probably won't need to be told that table variables don't participate. To me, it's a question of target audiences for the statement.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2010 at 9:35 am
Lynn Pettis (4/2/2010)
GilaMonster (4/2/2010)
GSquared (4/2/2010)
Saying "they don't participate in transactions" could be misleading. If you insert into a temp or permanent table from a table variable (either by itself or as part of a more complex query), there will be transactions, and they can be rolled back.There will be transactions, they can be rolled back, but changes made to the table variable won't be rolled back. Is there a better word than 'participate' to convey that?
How about:
Also, because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.
Temp tables have limited scope and aren't part of the persistent database, but they are subject to explicit rollbacks.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2010 at 9:37 am
GilaMonster (4/2/2010)
GSquared (4/2/2010)
I'd be specific. "Transactions in table variables can't be rolled back."I dunno, that could give the impression that failed inserts/updates don't roll back (insert 10 rows, row 8 fails, there will be 7 rows in the table variable afterwards)
'Table variables are not affected by explicit transaction rollbacks'?
English can be so hard to get right sometimes...
I like that version. That's even better. ("Even more betterer", since we're already playing with the limitations of English. Might even be "bettest".)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2010 at 9:42 am
GSquared (4/2/2010)
Lynn Pettis (4/2/2010)
GilaMonster (4/2/2010)
GSquared (4/2/2010)
Saying "they don't participate in transactions" could be misleading. If you insert into a temp or permanent table from a table variable (either by itself or as part of a more complex query), there will be transactions, and they can be rolled back.There will be transactions, they can be rolled back, but changes made to the table variable won't be rolled back. Is there a better word than 'participate' to convey that?
How about:
Also, because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.
Temp tables have limited scope and aren't part of the persistent database, but they are subject to explicit rollbacks.
But they are, tempdb is a persistent database (just recreated everytime SQL Server is restarted). Yes, I know, table variables are also created in tempdb as well.
We could go around and around on how to best word this, but Books Online tells us that table variables are not affected by a transaction rollback. Tables variables are part of a transaction that updates a table variable, and if that transaction fails, it will rollback, has to because of ACID.
I find this a good way to test destructive processes by wrapping them in a BEGIN TRANSACTION .. ROLLBACK block with the OUTPUT of the UPDATE/DELETE/INSERT being written to a table variable such that I can capture that information to a more permanent table outside the transaction for verification/validation of the process.
April 2, 2010 at 9:42 am
How about:
"Table variables are weird...not quite a variable...not quite a table. Go figure."
😀
April 2, 2010 at 9:48 am
GilaMonster (4/2/2010)
'Table variables are not affected by explicit transaction rollbacks'?
Operations performed on a table variable within a transaction are not undone if that transaction aborts.
April 2, 2010 at 10:03 am
Paul White NZ (4/2/2010)
GilaMonster (4/2/2010)
'Table variables are not affected by explicit transaction rollbacks'?Operations performed on a table variable within a transaction are not undone if that transaction aborts.
Except that can be read that operations don't rollback if they fail, which is untrue.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 13,771 through 13,785 (of 66,815 total)
You must be logged in to reply to this topic. Login to reply