SQL Server Transactions Examples

Create a Transaction with Savepoints

Write a SQL Server transaction that updates two tables, sets a savepoint, performs a third update, and rolls back to the savepoint if the third update fails.

Expected output: A transaction that partially rolls back to the savepoint on error.

Hint: Use BEGIN TRANSACTION, SAVE TRANSACTION, and conditional ROLLBACK.

Demonstrate Nested Transactions

Write SQL code that begins a transaction, starts a nested transaction, and commits both. Observe the behavior.

Expected output: Only the outermost commit finalizes the transaction.

Hint: Use multiple BEGIN TRANSACTION and COMMIT statements.

A transaction ensures that a group of SQL statements execute as a single unit, maintaining data integrity by committing all changes or rolling back if any statement fails.

Savepoints mark intermediate points within a transaction, allowing partial rollbacks to those points without rolling back the entire transaction.

Rolling back any nested transaction rolls back the entire transaction, including all nested levels.