Has this ever happened to you? You need to write up a t-sql statement that changes data but don't want to screw around with live production, test or even development data/procs you know that are golden? You have to write the proc/query/view/sql but don't want to change prestine data?!?!?!? WTF do you do?
Drum roll please.....................DU DU DU DU DAAAAAAA!!!!!!!!!! ...................Transactions!!!!!!!!!!!
Oh, you say you knew all about transaction before eh? But then why didn't you think of this before now then? Chances are, you probably already KNOW about transcations, you probably just never really thought about using them for your adhoc/whipping up some sql for testing/dev purposes.
All you want to is bracket your WIP (Work In Progress) t-sql with "being tran" and then "rollback tran". Yup, that's it! Simple eh? Told ya!
In the following example, I'm playing around with the sql I need in another script, and this sql drops an index. This is something I can do over and over, as long as my proc is WRONG!!!!!!!! But the second I get it right and then run the sql, I'm screwed for reproducing it again and tweaking/refactoring it (unless I write MORE sql to recreate the index). In other words, I can get it right once, then I have to do more work to get back to a state I can reproduce the same results, over and over again while I'm refactoring my t-sql. What a waste. With two simple commands (begin and rollback tran), you can quickly whip up some adhoc stuff and not be worried about damaging golden data.
begin tran
select count(*) from sys.indexes where object_id = OBJECT_ID('dbo.TBLCONSOLEEXPORT') and name = 'TBLCONSOLEEXP_IMAGEID'
if exists(select * from sys.indexes where object_id = OBJECT_ID('dbo.TBLCONSOLEEXPORT') and name = 'TBLCONSOLEEXP_IMAGEID')
DROP INDEX TBLCONSOLEEXPORT.TBLCONSOLEEXP_IMAGEID
select count(*) from sys.indexes where object_id = OBJECT_ID('dbo.TBLCONSOLEEXPORT') and name = 'TBLCONSOLEEXP_IMAGEID'
rollback tran
Now that you know that golden nugget to protect your golden data, go grab a coffee and get coding!