By default the database engine uses what’s known as an auto commit. Every T-SQL statement is committed or rolled back when it completes. 

The database engine will always use this auto commit functionality unless a transaction is explicitly specified with BEGIN TRAN.

Implicit transaction are rarely used in SQL server and when the option is turn on, most of the time it’s an error. With this implicit transaction option set to true, any of the following statements will cause a transaction. Alter table, create, insert, open, delete, revoke, drop,select, fetch, truncate table, grant and update. So that’s lots of things that you wouldn’t ordinarily associate with transactions like select statement, that will create a transaction for you.

Sources:

https://www.linkedin.com/learning/sql-server-performance-for-developers/implicit-transactions-why-they-re-terrible

Last modified: March 22, 2019

Author

Comments

Write a Reply or Comment