Understanding SAVEPOINT in SQL Server Transactions

What is a SAVEPOINT?

A SAVEPOINT is a marker within a transaction that allows partial rollback to that point without aborting the entire transaction.

It helps in managing complex transactions by enabling you to undo only a portion of the work if an error occurs.

• SAVEPOINT creates a named point in a transaction.
• You can rollback to this point if needed.
• The transaction remains active after rollback to a SAVEPOINT.

How to Use SAVEPOINT in SQL Server

To use SAVEPOINT, you first start a transaction, then define one or more savepoints.

If an error occurs, you can rollback to a specific savepoint instead of rolling back the entire transaction.

• Begin a transaction using BEGIN TRANSACTION.
• Create a savepoint using SAVE TRANSACTION savepoint_name.
• Rollback to a savepoint using ROLLBACK TRANSACTION savepoint_name.
• Commit the transaction with COMMIT TRANSACTION.

Example of SAVEPOINT Usage

Here is a practical example demonstrating SAVEPOINT in a transaction.