T-SQL Tuesday #200: It’s Bad If I See

Words: 578
Time to read: ~3 minutes

I think that I shall never see
this developer’s query is blinding me

– Joyce Kilmer (if she was a DBA)

Welcome to T-SQL Tuesday, the monthly blog party started by Adam Machanic ( blog ) and maintained by Steve Jones (blog | social media).

Each month, a different host chooses a topic for discussion. This month, we have Brent Ozar ( blog | social media ) asking us “if [we’re] looking at a query, it’s bad if [we] see…“

Musings

This post, inspired by a recent one by Hannah Vernon ( blog | social media ), is about WHILE loops and CURSORs.

Here’s my prejudiced take: if your query has WHILE loops and/or CURSORs, then I’m going to assume it’s bad.

Now, the astute among you will note what I just said with the blog post I linked to, and that’s a fair point. This is a knee-jerk reaction to opening up a piece of code and skimming its contents.

So far, in my relatively short stint as a DBA (I’m going to keep saying that no matter how long I am one, btw), I have only encountered 4 scenarios where these Row-By-Agonising-Row (RBAR) methods are acceptable.

1. See Hannah’s post – and it has to be commented that it’s an informed decision.
2. You need to call a stored procedure on the records – I don’t know how to get around that
3. You’re doing batching on mass writes – it’s more set-based inside RBAR than RBAR instead of set-based then
4. Temporal tables – I’m still salty about this…

The Feck It It’s Worth It Scenario

Read her blog post (I linked it again and everything.)

I’m normally an advocate for “write it anyway in case the way you say it gets through to someone”, but her blog posts have been on fire lately, and more people should be reading them.

Wrench and Sproc it

Apart from gutting the stored procedure and inserting its code in your set-based method, there’s no real way around this.

And if that stored procedure calls another stored procedure? Boy howdy, I ain’t touching that one with a ten-foot pole!

Turtles All the Way Down

Today is not Link to Other People’s Blog Day, but I’m going to pretend it is.

Here’s Micheal J Swart ( blog ) on batching.

I’m going to call this Set-By-Agonising-Set instead of Row-By-Agonising-Row.

Whatever name it goes by, whatever the reason for it, e.g. transaction log space, locking, etc., batching solves issues that come around at scale.

Saltier Than the Old Man and the Sea

Temporal tables… _deep breaths, Shane_

Do you know how to query temporal tables using the FOR SYSTEM_TIME syntax?

Well, you can do:

... FOR SYSTEM_TIME ALL ...
... FOR SYSTEM_TIME AS OF '<date time value> ...
... FOR SYSTEM_TIME AS OF @<date time variable>...

This is great…for uniformity. And all of our result sets are uniform, right?

However, if you have a table of results and you want a different FOR SYSTEM_TIME AS OF value for each record in the results table…

Granted, I have no idea how to implement this, or if it’s even possible, but I’d like it!

RBAR = Crowbar

If I’m looking at a query, it’s bad if I see WHILE loops, or CURSORs, or RBAR options without a documented or commented reason why.

If you’ve come across any other scenario, please let me know. The temporal table is the most recent one for me, and I’m relying on that saltiness to keep me young and preserved. Anything extra would be appreciated.