Home » SQL Server » SQL Server – SAVEPOINT TRANSACTION

SQL Server – SAVEPOINT TRANSACTION

SAVEPOINT SQL Server

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 TRANROLLBACK TRANCOMMIT TRAN

Let’s understand with an example:

Step 1: Sample data as below:

SQL Sample Data

SQL Sample Data

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
SAVE TRANSACTION

SAVE TRANSACTION



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

Select Statement

Select Statement

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

ROLLBACK TRAN P2

SelectStatement

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

Select Command

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.

Leave a Reply