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

Pass 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:

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.

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!

Advertisement