BEGIN TRANSACTION, COMMIT, and ROLLBACK

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Observe rollback on a temporary table before using commit. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

You have used BEGIN TRANSACTION and ROLLBACK since Chapter 8 to keep practice changes reversible. This chapter explains what those statements guarantee and how COMMIT differs from ROLLBACK.

Run the example

CREATE TABLE #Changes (ID int NOT NULL);
BEGIN TRANSACTION;
INSERT INTO #Changes VALUES (1);
ROLLBACK TRANSACTION;
SELECT COUNT(*) AS RowsAfterRollback FROM #Changes;
DROP TABLE #Changes;

Read it step by step

1. BEGIN starts a transaction covering the INSERT.
2. ROLLBACK undoes the inserted row but leaves the preexisting temp table.
3. COUNT(*) confirms zero rows afterward; COMMIT would have retained the change within this session.

Check the result

No canonical row is modified.

Practice

On a temp table, insert one row inside a transaction, COMMIT, and show the retained row.

CREATE TABLE #Changes (ID int NOT NULL);
BEGIN TRANSACTION;
INSERT INTO #Changes VALUES (2);
COMMIT TRANSACTION;
SELECT ID FROM #Changes;
DROP TABLE #Changes;