SQL Server supports both implicit and explicit transactions.

Implicit transactions are initiated when the system’s session option “Implicit Transactions” is set to ON. In this state, any of a number of commands, including Create, Insert, Update, Delete, Drop, and Select will automatically begin a new transaction. To finalize the transaction, the user must specifically issue the Commit Transaction command. Then the system will automatically begin a new transaction the next time one of those commands are issued. If Commit Transaction is not issued between commands, then the nested transactions are created inside of each other and will individually have to get committed or the entire nesting can be rolled back at once. 

Creating explicit transaction is the more common approach. When Implicit Transactions is set to OFF, SQL Server will not initiate a transaction on its own. Instead, an explicit Begin Transaction command must be issued before the beginning of the work unit. Then either a Commit Transaction or a Rollback Transaction command will finish it and either save the modifications to disk or return them to the prior state respectively.

Always try to use explicit transaction and avoid using implicit transactions. You have more control over explicit transaction.

Last modified: March 19, 2019



Write a Reply or Comment