Jan Arnoldus (8/24/2013)
Hi Jeff,We did this test to check the overall response time on high number of consecutively updates, which is similar to the pattern the table is used in production.
We thought that the semicolon at the end of each line will cause an implicit commit. Are we wrong in such an assumption?
What is the difference between running a 1000 update as in the first example and wrapping it in a transaction?
BY the way I checked the Implicit Transaction for both SQL server and my query sessions and it is OFF.
Thanks.
Implicit Transactions have to committed explicitly, so the first DML (or DDL, with some exceptions) after implicit transactions is switched on or a previous implicit transaction is committed or rolled back starts a new implicit transaction and subsequent statements are part of that same transaction until it is ended either by an explicit commit or rollback or by an error that forces rollback.
When implicit transactions is off and there is no current transaction, a statement that would otherwise have begun an implicit transaction becomes a transaction on its own - it starts the transaction and also either commits it or rolls it back.
Rotten terminology, really - both the single statement transaction when implicit transactions is set off and the multi-statement when it is on are actually begun implicitly; the single statement one is also terminated implicitly, so it actually seems more entitled to the designation "implicit transaction" than what you get when implicit transactions is switched off. It's not at all surprising that people sometimes are confused about what "SET IMPLICIT_TRANSACTIONS" means.
Tom