SQL Server paranoia mode

This post is for Tony!

Worried about accidentally overwriting critical data with a typo in your database commands?

SQL Server Management Studio has an option for that: SET IMPLICIT TRANSACTIONS, which you’ll find (in 2008 / 2008 R2 at least) under Tools / Options / Query Execution / SQL Server / ANSI.

SQL Server implicit transactions option

Anything you do (from the next query window you open) will automatically be in a transaction, so you can ROLLBACK if you realise you’ve done the wrong thing.

Be warned, you’ll need to get into the habit of manually COMMITting everything. Don’t be tempted to just add the COMMIT at the bottom of your query… that would defeat the purpose.

It’ll prompt you do to so if you close a query window without having done a ROLLBACK or COMMIT. It can get a little irritating, but knowing you can’t accidentally trash all your data may give you piece of mind in return.

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.