Thursday, April 25, 2019 ..:: Home ::.. Register  Login

Peter Henry on Facebook  Peter Henry on LinkedIn  Peter Henry on Twitter

All blog entries are the opinions of the author and do not necessarily reflect the opinions of their employer. All the code presented is for explanation and demonstration purposes only. Any damages incurred to your site and/or data are not the responsibility of the author. Every effort is taken to ensure the code properly compiles, however sometimes there are some hiccups and you might be required to do your own debugging.
   TechTidBits (Blog)  

Do you like to do adhoc t-sql or to do "back of the napkin" kind of queries?

Apr 22

Written by:
Wednesday, April 22, 2009 10:08 PM  RssIcon

As you're developing code/sql, do you tend to "fart around" with your t-sql in Managment Studio?  If you do, I'm sure you've ruined some classic/prestine data, right?  Read on for a quick trick to stop that from happening again!

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')
      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!

Location: Blogs Parent Separator TechTidBits

1 comment(s) so far...

Re: Do you like to do adhoc t-sql or to do "back of the napkin" kind of queries?

If you're doing SQL Server 2000 and 2005 development, this is helpful.

By phenry on   Friday, July 17, 2009 1:12 PM

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Add Comment   Cancel 
Copyright 1999-2012 by   Terms Of Use  Privacy Statement