Committing Work

Question

Post reply

Committing Work

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Hany Helmy SSChampion Points: 13921 More actions · Answer 1

Koen Verbeeck (11/20/2015)
Learned something new. Thanks Steve!

+ 1

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 2

Luis Cazares (11/20/2015)
It actually should work with previous versions as well. Documentation for SQL Server 2000 includes it, but I couldn't test on anything older than 2008R2.
https://technet.microsoft.com/en-us/library/aa258231(v=sql.80).aspx
It's weird that this is so old yet so unknown.

Worked with 2012 SP2.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

TomThomson SSC Guru Points: 104773 More actions · Answer 3

Luis Cazares (11/20/2015)
It actually should work with previous versions as well. Documentation for SQL Server 2000 includes it, but I couldn't test on anything older than 2008R2.
https://technet.microsoft.com/en-us/library/aa258231(v=sql.80).aspx
It's weird that this is so old yet so unknown.

I've used it in SQL 2000, 2008 (and 2008 R2, 2012 and 2014). Current MS documentation says it works in all versions after SQL 2008 - that's in the page referenced in the explanation, so it's hard t unerstand why the explanation says SQL 2014 and onwards. I don't know whether it worked in SQL 6.0, 6.5, or 7.0, but I'm sure it did in SQL 2005 although I never used 2005 so can't guarantee it did.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 4

bsproat (11/20/2015)
Interesting that this doesn't work
BEGIN TRANSACTION;
UPDATE
MyTable
SET
test = 9;
COMMIT WORK2
GO
But this does
BEGIN TRANSACTION;
UPDATE
MyTable
SET
test = 9;
COMMIT TRANSACTION WORK2
GO
I got it right not knowing about WORK but assuming they were the same.

7 characters plus number - that's a lot of typing. I save 3 characters by using Oink instead of MyTable. 😛

Actually, all this named transaction stuff is a bit of a joke. I might think that someone who writes code like the following (even with the comments on the commit statements) is being silly, but it will work - technically there's nothing wrong with it:

begin tran outer

--do some ddl queries

begin tran middle

--do some more ddl queries

begin tran inner

--do yet more ddl queries

commit tran outer -- this commits inner

--do yet more ddl queries still

commit tran inner -- this commits middle

--and some more ddl again

commit tran middle -- this commits outer

And of course you can replace any of those commit statements by "commit" or "commit tran" or "commit work" or commit transaction" or "commit tran bananas" and it makes not the slightest change to how the engine executes them. A syntactically valid commit statement commits current innermost transaction regardless of whether is specifies the name of that transaction, the name of a different transaction, a name which belongs to no transaction, or no name, or uses the WORK keyword. In other words, the transaction names are only documentation - like comments, they have no effect at all on execution.

So there you go - you now have a new T-SQL obfuscation technique in your repertoire (if you didn't know this one already). :hehe:

Tom

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

It seems to work just fine on 2008 as well. I would guess that once the COMMIT word is reached, the transaction is done. The word "WORK" doesn't seem to cause any grief.