This tip is one of those “Why didn’t I think of that before” things when I saw it being used first. You see, sometimes you’re on this production database and you need to run some SQL statements in Microsoft SQL Management Studio. Statements that if you mess them up will end up ruining your customer’s day, and most certainly yours too.
Of course you’ve already taken a backup, but that isn’t perfect. It takes time to restore those and the most recent changes might be lost. The database might also be humongous, making backup & restore operations rather annoying.
Enter:
BEGIN TRANSACTION
You know this! Before you run any statement doing anything destructive on your database, first run the BEGIN TRANSACTION statement. It feels like spring doesn’t it? With fluffy little bunnies hopping around merrily in the green grass and yellow flowers.
Whatever you run next, nothing will actually happen until you run:
COMMIT TRANSACTION
If in the meanwhile however that delete statement turns out to act like the Spanish Flue and wipes out half of the precious records stored in your database table, there’s always:
ROLLBACK TRANSACTION
Undo. Restore from last save. You don’t lose a life.
Awesome. More fluffy bunnies.
One thing to keep in mind though. As long as your transaction isn’t committed your statements will be putting locks on rows and perhaps even tables. The longer you keep those, the more chance other people’s queries will have to wait for you to release those locks and might even time out. This is bad. So keep your transaction trickery as short as possible.
For more details on those magical statements, see MSDN.