IMPLICIT_TRANSACTIONS & BEGIN TRAN

• TomThomson

  SSC Guru

  Points: 104773

  More actions

  January 12, 2015 at 2:01 pm

  #1769638

  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
  

• Thomas Abraham

  SSChampion

  Points: 10761

  More actions

  January 12, 2015 at 2:38 pm

  #1769646

  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

• Miles Neale

  SSChampion

  Points: 13147

  More actions

  January 12, 2015 at 3:15 pm

  #1769651

  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!

• Eirikur Eiriksson

  SSC Guru

  Points: 182946

  More actions

  January 12, 2015 at 3:16 pm

  #1769653

  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;-)

• Marcia J

  SSCertifiable

  Points: 5649

  More actions

  January 12, 2015 at 5:59 pm

  #1769682

  Good question. I got it wrong :(, but I learned something :-).

• Andre Ranieri

  SSCrazy

  Points: 2819

  More actions

  January 16, 2015 at 12:45 pm

  #1770868

  +2 - thanks for the review. I always seem to forget this one.

  Andre Ranieri