SQL Server – SAVEPOINT TRANSACTION

The SQL Server SAVEPOINT are used to roll back transactions to a specified point without rolling back the entire transaction.

Syntax:

SAVE TRAN | TRANSACTION  SAVE_POINT_NAME

Note :  BEGIN TRAN,  ROLLBACK TRAN,  COMMIT TRAN

Let’s understand with an example:

Step 1: Sample data as below:

Step 2: Delete two id’s from Students table using SAVEPOINT P1 & P2, write below queries & execute both together.

SAVE TRANSACTION P1
Delete FROM Students WHERE studentid=2

SAVE TRANSACTION P2
Delete FROM Students WHERE studentid=3

Step 3: Two rows deleted successfully, select Students table.

Step 4:   ROLLBACK Transaction SAVEPOINT P2, execute below command and select Students table.

ROLLBACK TRAN P2

As you saw, StudentId 3 (Savepoint P2) has been rolled back successfully.

Step 5: Now, ROLLBACK Transaction SAVEPOINT P1, execute below command and select Students table.

ROLLBACK TRANSACTION P1

Now, StudentId 2(Savepoint P1) has been rolled back successfully.

Note :

In above example we used two savepoint together and after that we rolled back transaction P1 & P2 individually.

So in multiple savepoint case, if we direct rolled back first Savepoint, it rollback all transaction till first save point.  That means second transaction automatically rolled back.




Hope you enjoy the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.