SAVEPOINT in SQL Server Transaction

A SAVEPOINT in SQL Server is a named checkpoint within a transaction that allows partial rollback without undoing the entire transaction. It is created using the SAVE TRANSACTION statement and can be referenced later using ROLLBACK TRANSACTION SavePointName. SAVEPOINTs are useful in complex business processes where certain steps can be undone while preserving earlier successful work. Unlike COMMIT, a SAVEPOINT does not make data permanent, release locks, or increase @@TRANCOUNT. It simply marks a position in the transaction log to which SQL Server can roll back if needed. In enterprise applications, SAVEPOINTs are frequently used in ETL jobs, financial transactions, and stored procedures to implement granular error recovery and partial rollback strategies.

Suppose we are writing notes in a notebook before an exam. Scenario Without SAVEPOINT we write:

Ø  Chapter 1 Notes

Ø  Chapter 2 Notes

Ø  Chapter 3 Notes

And Suddenly Tea falls on notebook ☕ we lose everything.

Ø  Chapter 1 ❌

Ø  Chapter 2 ❌

Ø  Chapter 3 ❌

This is similar to ROLLBACK TRANSACTION Entire transaction is rolled back.

Scenario With SAVEPOINT

Suppose after every chapter you save a copy.

Ø  Chapter 1 Completed

o   📌 Savepoint 1

Ø  Chapter 2 Completed

o   📌 Savepoint 2

Ø  Chapter 3 Started

Now tea falls on notebook. We don't need to rewrite everything. We restore from Savepoint 2.

Result:

Ø  Chapter 1 ✔

Ø  Chapter 2 ✔

Ø  Chapter 3 ❌

Only the latest work is lost.

Transaction = Writing Notebook SAVEPOINT = Ctrl + S (Checkpoint) ROLLBACK TO SAVEPOINT = Restore Last Saved Version ROLLBACK TRANSACTION = Throw Away Entire Notebook

 Let’s see the demo

Creating a table

IF OBJECT_ID('dbo.Employee') IS NOT NULL     DROP TABLE dbo.Employee; GO CREATE TABLE dbo.Employee (     EmployeeID INT primary key not null,     EmployeeName VARCHAR(50) ); GO

Inserting few records

BEGIN TRAN; INSERT INTO dbo.Employee VALUES (1,'John'); PRINT 'Employee 1 Inserted'; SAVE TRANSACTION SP1; PRINT 'SAVEPOINT SP1 Created'; INSERT INTO dbo.Employee VALUES (2,'David'); INSERT INTO dbo.Employee VALUES (3,'Mike'); PRINT 'Employee 2 and 3 Inserted';  SELECT * FROM dbo.Employee;

Transaction is still open.

Now ROLLBACK TRANSACTION SP1; let see

ROLLBACK TRANSACTION SP1;

SELECT * FROM dbo.Employee;

See here record before save point data is committed. After that it is rollbacked. When we are saying that Savepoint is rollback not yet whole transaction is rollback. Opening the another session and seeing the data.

Table is locked because transaction is not yet completed. Now we can see the uncommitted data.

2 rows already rollback.

Now we are committing the open transaction.

In other session we can see this data

See the example with try… catch.

BEGIN TRY     BEGIN TRAN;     INSERT INTO dbo.Employee VALUES (2,'Bagesh');     SAVE TRANSACTION BeforeRiskyStep;      INSERT INTO dbo.Employee VALUES (2,'Rajesh');     COMMIT; END TRY BEGIN CATCH     PRINT ERROR_MESSAGE();     ROLLBACK TRANSACTION BeforeRiskyStep;     COMMIT; END CATCH;

Here 1 row inserted and one row rollbacked. See the records in the table.

Insert Bagesh           → Success
SAVEPOINT Created     → Success
Duplicate Insert      → Error
Rollback to SAVEPOINT → Executed 

Commit Remaining Work → Success