T-SQL Tuesday Roundup: You Know A Query’s Gonna Be Bad When…

For this month’s T-SQL Tuesday, I invited y’all to tell me how you know a query’s gonna be bad when you open it.

Riccardo Perico and Shane O’Neill says WHILE loops and/or CURSORs are pretty good indicators. I agree, because especially when you’re opening code in the year 2026, and someone still hasn’t figured this out (or the code hasn’t been changed in decades), then you’re gonna be in for a rocky road. Side note: this is one of the times where I really love AI, because it’s great at making very quick work of the horrible task of turning RBAR code into set-based code.

Rebecca Lewis hates seeing functions in WHERE clauses, and I’d point out that even though SQL Server 2019’s scalar function inlining was supposed to fix some of this, the kb article showing its problems keeps expanding, and the fix gets blocked in more and more scenarios, so… yeah. The query’s probably gonna be bad.

Dualcore DBA does an in-depth example showing lots of analysis about why scalar functions are a warning sign, but with a surprising conclusion: in his specific example, it turns out removing the function isn’t the problem. I’d still agree that the function is a warning sign though.

Reitse Eskens chimes in on functions. The fact that so many people see scalar UDFs as a warning sign is testimonial as to why Microsoft did the right thing by trying to fix it with 2019’s inlining, but … they just haven’t done enough of the right thing there.

Vlad Drumea has a laundry list of warning signs, and he starts the list with DISTINCT in queries with multiple JOINs. I always thought of DISTINCT by itself as a warning sign, but his qualification about the joins makes so much more sense. If someone just needs the distinct values in column in a specific table, that’s not nearly as bad as the sorting that has to happen after fetching data from multiple tables.

Andy Brownsword twitches at the sight of old-school join syntax, the comma-delimited table list with all the join conditions and the filtering conditions tossed in together in the WHERE clause. Those always make think of the Ghostbusters quote, “Dogs and cats living together! Mass hysteria!”

I groan when I see a text changelog at the top of a stored procedure or function, listing a bunch of initials and dates going back to the Frutiger Aero era.

Andy Bek has a visceral gut reaction when he sees the looooooong query. I laughed because I usually see this coming in client environments when I say, “Okay, let’s open up the source of that stored proc and – ” and immediately people on the call groan and say things like, “Oh no, not that one. We don’t even like to look at that one, because it’s so hard to understand.” The longer it takes you to get the full context in mind about what the query’s doing, the less likely you are to fix it – so these things just soldier on for years, getting worse.

Tom Zika starts by looking for signs of AI generated queries, but he’s quick to point out he’s not anti-AI. I think of this the same way I think of code that was clearly copy/pasted from Stack Overflow: it’s good that someone was looking for help, but they just gotta keep going and finish connecting the dots.

Hugo Kornelis equates left joins with red flags: we can deal with a couple/few of them, but when it’s the only thing you see inside the query, you should probably walk away before you get into a serious relationship look carefully at the rest of the query.

Steve Jones points out an even more suspicious join type. If you see even one CROSS JOIN, you’re probably going to be in for a bad time. (And, like so many of the other authors, points out scalar functions.)

Martin Guth looks at queries that terminated optimization early, and he talks about how he approaches writing (and rewriting) queries in a modular way to make it easier on the optimizer.

Magdalena Bronowska says it’s less about something dramatic, and more about small choices that make code harder to read, maintain, and trust.

Todd Kleinhans gets suspicious when there’s a lack of comments and sample syntax calls. I’d add, if there’s a syntax call and it looks woefully out of date, that usually means people still copy/paste it and as long as it still runs fast, they think they’re okay.

Louis Davidson made a list of bad smells, but realized that for each one, he’s really suffixing it with, “without a coherent comment.” There are a lot of things in SQL that seem bad, but they have good uses in the right use cases, and comments help you justify that.

Nigel Foulkes-Nock has a few things, starting with long meandering queries with multiple steps that end up doing filtering at the end – on data they really could have filtered out in the very first step. I come across this more often than you would believe! Just a couple of weeks ago, I was “tuning” a batch process that put hundreds of millions of rows into a temp table, only to go through and delete them in one pass after another.

Deb Melkin has a good list of bad things, and says the sad thing is when you find them all in that one query, hahaha.

Rob Farley spots the repeated one-letter aliases like a, b, c and knows bad code is coming, along with repeated update passes through the same table over and over again, processing one column at a time.

Mala Mahadevan has a laundry list too, including left joins followed by filters that turn the join into an inner join. Ooo yeah, that’s an instant sign that something was copy/pasted and never really thought out.

Aaron Bertrand is suspicious about RIGHT OUTER JOINs, and I gagree. I originally meant to type “I agree” but I mistyped “I gagree”, and I have to let that stand, because I do gag when I read “right outer join”.

Chad Callihan sees leading percent signs in like searches and knows he’s in for a bumpy ride. When people tell me they really gotta do this, and they want it to be fast, I point them to Paul White’s trigram search method.

Andy Levy looks for hard-coded config values at the top of a query that gets loaded in just before the code gets run – but never makes it into a real user table or source control.

Chris Burkholder looks for implicit conversions that keep happening repeatedly, despite the team having been trained on it, and just ignoring the issue until it takes production down.

Jeff Mlakar gets concerned about too many indexes, and I have questions but his post was late and he was ashamed of that, so we’re gonna let it slide, hahaha.

Finally, the only way to wrap up the end of this post would be to mention that Scott Peters looks for ending semicolons, which my first reaction was to say that’s ridiculous – but then I realized I’ve been doing it for so long that not ending queries with semicolons would indeed really seem weird to me!

Here’s to another 200 months of T-SQL Tuesday tips & tricks. Thanks to everyone who took time out of their routine this month to give back to the community and keep moving everyones’ skills forward.