SQL Server: Taking Advantage of Transactions

I won’t go in depth about SQL Server transactions, but rather what you can do to apply destructive changes safely, like deleting records or updating them. It’s highly advised that direct queries don’t get run on production databases but there are situations where that is required.

Wrapping with a Transaction

Here is a simple quick and dirty approach to running a query to fix data:

begin tran;

-- destructive query here

go
rollback tran;

Simple, right? You wrap your query around with a transaction, and you roll it back without committing. What this will do is allow you to find any errors or see if you get the expected result.

Of course, it is always encouraged that queries are scripted and tested in dev environments rather than applying changes directly to production databases. But if you have to do it, this is one safe way.