The SQL Server SAVEPOINT are used to roll back transactions to a specified point without rolling back the entire transaction.
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.
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.