Russ Thomas – SQL Judo

The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded

TSql Tue 60: Hey Ma, looky here what I learnt!!

T-SQL TuesdayPass Summit has come and gone.  I am definitely suffering from the post-PASS blues.  My head is all full of interesting stuff but also… I’m exhausted.  Hats off to those who are able to pull themselves out of a week of SQL bingeing and write up something they learned in a coherent way. Also, hats off to our TSQL Tuesday host Chris Yates who provides this month’s topic. “Share something you’ve learned”.

If I was really ambitious I would tell you all about the machine learning session I attended and the advanced predictive statistics that it facilitates.  Turns out however that I am just not that smart.  That session made Bob Ward’s deep dive into SQLOS almost seem palatable.

I will instead share one simple trick that I am almost embarrassed I didn’t know about before.  I didn’t think after being in this industry for as long as I have (both casually and professionally) I’d pick up something as simple as the following.  I tried to convince myself it must be a new feature but nope, checking BOL it’s been around for a long time.

GO < this many times >

If you’ve generated scripts, pushed out deployments, or really done any type of work with SQL Server in SSMS you’ve almost surely used the GO command.  GO isn’t part of the TSQL language but is a command recognized by the osql and sqlcmd utilities as well as SSMS as a batch separator.  When GO is encountered all previous TSQL statements (since last go) are passed to the SQL Engine as a batch.

What I learned recently however is that you can follow the GO command with a integer and the batch will be sent repeatedly to the SQL Engine that many times.  Consider the following:

go

That’s just cool. I can’t recall how many times I’ve created some type of crappy loop to insert values, do batch deletes, or some other type of repetitive activity to generate test data, archive a table, or solve a variety of other ad-hoc problems. I literally never knew I could attack the problem this way.

batchdelete

Note, this is for ad-hoc work, but as a utility in your tool belt – it definitely has it’s place. Thanks PASS presenters for teaching me this and a million other things last week!

One comment on “TSql Tue 60: Hey Ma, looky here what I learnt!!

  1. Chris Yates
    November 14, 2014

    Nice Russ, thanks for participating this month. I’m sure a lot of people can glean some good aspects from it and incorporate this into their arsenal.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Information

This entry was posted on November 11, 2014 by in 31 Days of SSMS.
%d bloggers like this: