Implicit transaction:

  • The transaction remains in effect until you issue a COMMIT or ROLLBACK statement. After the first transaction is committed or rolled back, the instance of the Database Engine automatically starts a new transaction the next time any of these statements is executed by the connection. The instance keeps generating a chain of implicit transactions until implicit transaction mode is turned off.
  • It can be set using SET IMPLICIT_TRANSACTIONS { ON | OFF}
  • Under a multiple active result sets (MARS) session, a Transact-SQL implicit transaction becomes a batch-scoped transaction. If the batch-scoped transaction is not committed or rolled back when the batch completes, SQL Server automatically rolls back the transaction. 

Explicit Transaction:

  • An explicit transaction is one in which you explicitly define both the start and end of the transaction.
  • SET IMPLICIT_TRANSACTIONS { ON | OFF}
  • DB-Library applications and Transact-SQL scripts use the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK Transact-SQL statements to define explicit transactions.