How are transactions handled in SQL Server?

10 months ago

Noah Thompson

1 minute

In SQL Server, you can use the BEGIN TRAN, COMMIT, and ROLLBACK statements to manage transactions. Here is a simple example:

BEGIN TRAN

INSERT INTO table1 (column1, column2) VALUES (value1, value2);

UPDATE table2 SET column1 = value1 WHERE column2 = value2;

COMMIT;

In the example above, BEGIN TRAN means starting a transaction, COMMIT means committing a transaction, and ROLLBACK means rolling back a transaction. If an error occurs during the transaction execution or if it is necessary to undo previous actions, the ROLLBACK statement can be used to roll back the transaction and restore the database to its state before the transaction began.

Furthermore, SQL Server also supports the SAVEPOINT statement, which allows you to set a savepoint within a transaction to partially rollback during the transaction execution. For example:

BEGIN TRAN

INSERT INTO table1 (column1, column2) VALUES (value1, value2);

SAVEPOINT save1;

UPDATE table2 SET column1 = value1 WHERE column2 = value2;

ROLLBACK TO save1;

COMMIT;

In this example, SAVEPOINT save1 represents a checkpoint that can be used to rollback to after executing an UPDATE statement, without affecting data that was inserted before.