SQL Server Interview Question #147

What is a SAVEPOINT in SQL Server?

Transactions, Locking, Blocking & Deadlocks Senior Advanced

Detailed Explanation

A savepoint marks a location within a transaction so part of the transaction can be rolled back without necessarily rolling back all work performed since the transaction began.

SQL Server creates a savepoint using SAVE TRANSACTION. Savepoints can be useful in complex procedures, although transaction design should remain as simple and short-lived as possible.

Code Example

BEGIN TRANSACTION;

UPDATE dbo.Products
SET Price = Price + 10
WHERE Id = 1;

SAVE TRANSACTION PriceUpdate;

UPDATE dbo.Products
SET Price = Price + 100
WHERE Id = 2;

ROLLBACK TRANSACTION PriceUpdate;

COMMIT TRANSACTION;