SQL Server Transactions: Understanding ROLLBACK

What is ROLLBACK in SQL Server?

ROLLBACK is a transaction control statement that reverses all changes made in the current transaction.

It restores the database to the state before the transaction began, discarding any uncommitted changes.

• Used to undo operations within a transaction.
• Ensures atomicity and consistency of data.
• Can be triggered manually or automatically on errors.

Syntax and Usage

The basic syntax for ROLLBACK is straightforward and must be used after a transaction has started.

You begin a transaction with BEGIN TRANSACTION, perform your SQL operations, and then either COMMIT or ROLLBACK.

• BEGIN TRANSACTION;
• SQL statements (INSERT, UPDATE, DELETE);
• ROLLBACK TRANSACTION; -- to undo changes
• COMMIT TRANSACTION; -- to save changes

Practical Example

Suppose you want to transfer money between two accounts. If an error occurs during the transfer, you need to rollback to prevent inconsistent data.