January 12, 2015 at 2:01 pm
Hugo Kornelis (1/12/2015)
I never liked implicit transaction, and I never will.I am also glad that Carlo used PRINT, not SELECT. I thought that using SELECT would have started the implicit transaction, but I tested after answering this question and nothing changes if I use SELECT instead of PRINT. That was a surprise to me.
It shouldn't be a surprise, because there is an exception for select stating that if it doesn't access a table it won't start an implicit transaction.
What was a surprise for me was that for the purposes of this exception table variables count as tables; that seems a bit crazy - why should selecting something from a table variable start a transaction? Not because it's logical, so perhaps just because it's easiest to code it that way and logic of course doesn't matter.
Tom
January 12, 2015 at 2:38 pm
Hugo Kornelis (1/12/2015)
I thought that using SELECT would have started the implicit transaction, but I tested after answering this question and nothing changes if I use SELECT instead of PRINT. That was a surprise to me.
Since Hugo admitted it, so will I. And thanks to OP for the question.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
January 12, 2015 at 3:15 pm
TomThomson (1/12/2015)
Hugo Kornelis (1/12/2015)
I never liked implicit transaction, and I never will.I am also glad that Carlo used PRINT, not SELECT. I thought that using SELECT would have started the implicit transaction, but I tested after answering this question and nothing changes if I use SELECT instead of PRINT. That was a surprise to me.
It shouldn't be a surprise, because there is an exception for select stating that if it doesn't access a table it won't start an implicit transaction.
What was a surprise for me was that for the purposes of this exception table variables count as tables; that seems a bit crazy - why should selecting something from a table variable start a transaction? Not because it's logical, so perhaps just because it's easiest to code it that way and logic of course doesn't matter.
What was a surprise for me was that either of you were surprised! 🙂
Not all gray hairs are Dinosaurs!
January 12, 2015 at 3:16 pm
Thomas Abraham (1/12/2015)
Hugo Kornelis (1/12/2015)
I thought that using SELECT would have started the implicit transaction, but I tested after answering this question and nothing changes if I use SELECT instead of PRINT. That was a surprise to me.Since Hugo admitted it, so will I. And thanks to OP for the question.
The select will not increment the transaction count, to test this, add a rollback between the sections.
😎
SET NOCOUNT ON;
PRINT @@TRANCOUNT
SET IMPLICIT_TRANSACTIONS ON
PRINT @@TRANCOUNT
BEGIN TRAN
PRINT @@TRANCOUNT
SET IMPLICIT_TRANSACTIONS OFF
PRINT @@TRANCOUNT
COMMIT
PRINT @@TRANCOUNT
rollback
select @@TRANCOUNT
SET IMPLICIT_TRANSACTIONS ON
select @@TRANCOUNT
BEGIN TRAN
select @@TRANCOUNT
SET IMPLICIT_TRANSACTIONS OFF
select @@TRANCOUNT
COMMIT
select @@TRANCOUNT
rollback
Output
0
0
2
2
1
-----------
0
0
2
2
1
No fan of implicit transactions nor in fact implicit anything when it comes to SQL Server/T-SQL
BTW thanks for the question;-)
January 12, 2015 at 5:59 pm
Good question. I got it wrong :(, but I learned something :-).
January 16, 2015 at 12:45 pm
+2 - thanks for the review. I always seem to forget this one.
Andre Ranieri
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply