Previewing changes and rolling them back

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Compare a value before, during, and after a rolled-back change. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

Run the example

DECLARE @Before decimal(10,2) = (SELECT ListPrice FROM dbo.Products WHERE ProductID = 1);
BEGIN TRANSACTION;
UPDATE dbo.Products SET ListPrice = ListPrice + 5.00 WHERE ProductID = 1;
DECLARE @During decimal(10,2) = (SELECT ListPrice FROM dbo.Products WHERE ProductID = 1);
ROLLBACK TRANSACTION;
SELECT @Before AS BeforePrice, @During AS DuringPrice,
       ListPrice AS AfterRollbackPrice
FROM dbo.Products WHERE ProductID = 1;

Read it step by step

1. DECLARE @Before decimal(10,2) creates a variable: a named, typed value that lasts until the end of the batch. Variable names start with @. Here it saves the starting price.
2. Change the single row inside a transaction and capture its temporary value in a second variable, @During.
3. ROLLBACK restores the row; the final SELECT compares before, during, and after.

Check the result

BeforePrice and AfterRollbackPrice match in the unmodified dataset.

Practice

Repeat for ProductID 2 with a temporary increase of 10.00.

DECLARE @Before decimal(10,2) = (SELECT ListPrice FROM dbo.Products WHERE ProductID = 2);
BEGIN TRANSACTION;
UPDATE dbo.Products SET ListPrice = ListPrice + 10.00 WHERE ProductID = 2;
DECLARE @During decimal(10,2) = (SELECT ListPrice FROM dbo.Products WHERE ProductID = 2);
ROLLBACK TRANSACTION;
SELECT @Before AS BeforePrice, @During AS DuringPrice,
       ListPrice AS AfterRollbackPrice
FROM dbo.Products WHERE ProductID = 2;