SQL Server: Set IMPLICIT TRANSACTIONS, Not require BEGIN TRANSACTION

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing the use of SQL Server IMPLICIT TRANSACTIONS.

When you set the IMPLICIT TRANSACTION, database engine automatically considers a new transaction after every COMMIT/ROLLBACK.
We are writing DML statements between BEGIN and COMMIT/ROLLBACK. If our logic is divided into the different batches, managing BEGIN…COMMIT is difficult.

Just, you can set IMPLICIT TRANSACTION, and do COMMIT/ROLLBACK for each of your batches.

Note: Once you set IMPLICIT TRANSACTION, all your query tables will lock because IMPLICIT TRANSACTION applies the standard lock on the table. I can see only one feature is, not require to write BEGIN TRANSACTION that’s it.

Check the below demonstration:

Set IMPLICIT TRANSACTION:

Create a sample table:

Do COMMIT:

Now insert a sample record and do ROLLBACK:

Test one more COMMIT:

Try ROLLBACK:
You will get an error because above there is no any open transaction.

Try one more ROLLBACK:

Check the final result: