Press "Enter" to skip to content

Category: Syntax

Wrong Outer Joins

Published 2026-07-17 by Kevin Feasel

Aaron Bertand only likes the right kind, by which I mean the left kind:

The headline is probably unfair and is not meant to imply that a RIGHT OUTER JOIN is wrong. But when I see a RIGHT OUTER JOIN, my first thought is, “the rest of this review will probably be harder than it needs to be.” I find that it makes queries harder to read, because most people naturally read queries left-to-right. With a left join, the “important” table is on the left, and the query is saying, “give me everything from this table, and maybe something from this related table.” With a right join, I have to mentally flip things around to understand which table is important.

Aaron is absolutely right about left-handed scissors. I extend this as well to can openers and a half-dozen other tools that fit very well in the right hand but are quite awkward for southpaws. And don’t get me started on writing.

Aaron is kinder toward RIGHT OUTER JOIN than I am. I’m not convinced there’s ever a reason that we should use RIGHT OUTER JOIN. LEFT OUTER JOIN is much easier for humans to interpret, and (save for very specific scenarios) we should optimize code for human interpretability over pretty much anything else. And yes, that includes (again, save for very specific scenarios) performance.

Leave a Comment

Copy-Pasta’d Temp Tables and More Fun

Published 2026-07-17 by Kevin Feasel

Andy Levy shares some thoughts:

I’ve spent a lot of time over the past 8 or years trying to “right the ship.” Systems that have been built and evolved over 10-15 years and the cracks are starting to show. Yes, there’s always the hot spot code that desperately needs attention, the stored procedure that runs in 6 hours but could be 20 minutes with the right adjustments. But I’m looking at a macro level today, more “operational” than “surgical.” When I see __ in a chunk of code, it’s a signal to me that there are overarching problems in how the whole system I’m working on was built and it’s going to take me a good, long while to undo that to deliver constant performance as data grows or improve maintainability.

Andy’s main topic is pre-populated temp tables serving as lookup tables in queries. Though if Andy wants eldrich temp table horrors, I raise him global temp tables (##table) created from a separate session and a SQL Agent job that runs every minute to create it, with people ignoring the failures because “That’s how it’s supposed to work.”

I’ve never seen that in practice, but now I kind of want to do it.

Bonus comment: leading semi-colons for CTEs. I rarely do that, but when I do, it’s because a semi-colon on the same line as a batch separator doesn’t count. In other words,

GO;

WITH records as (...)

returns an error. There might be some workable variant, admittedly, but in those cases, I do put the semi-colon in front of the CTE. The rest of the time, when I know there isn’t a batch separator right before the common table expression, I of course don’t. Commas and semi-colons belong at the end, not the beginning.

Leave a Comment

Bad Query Pattern: Wildcards on Both Ends

Published 2026-07-17 by Kevin Feasel

Chad Callihan covers this month’s T-SQL Tuesday topic:

A lot of SQL queries or code can still “work,” but that doesn’t mean it’s good, especially with so much vibe coding and the like going on these days. When I think of signs I’ve noticed when seeing a query for the first time, one thing that will get my attention (besides seeing NOLOCK throughout) is when a string is being searched for surrounded by percent signs.

I’ve had instances of working with someone that was looking for certain error logs. We may know the log record starts with “Error 123” and could search a field for “Error 123%” in our query.

Click through for the consequences of a search on “Error 123%” versus “%Error 123%”. Sometimes it’s simply necessary to do the full search, but if you find yourself doing that frequently, it’s a sign that you could design the table better.

Leave a Comment

Bad Query Signals

Published 2026-07-14 by Kevin Feasel

Mala Mahadevan takes advantage of an extra week:

I just managed to get a post in for this landmark T-SQL Tuesday, hosted by Brent Ozar. Brent was kind enough to keep the submission window open for two weeks instead of the usual one, and I was able to sneak a post in last – minute.

His invitation is to write about the things that immediately stand out as “bad signs” when reviewing a SQL query.

Click through for Mala’s list. It’s a good list. While some items Mala calls out are defensible and quite reasonable, there are some of them (such as a LEFT OUTER JOIN whose columns show up in the WHERE clause for filtering) that are simply not.

Leave a Comment

Checking Query Options from Query Store

Published 2026-07-13 by Kevin Feasel

Michael Bourgon sets ANSI_PADDING:

We know that SQL Server can cache multiple query plans for the same query based on the SET_OPTIONS for that query, and that SSMS doesn’t have the same options as the standard library. (https://www.sommarskog.se/query-plan-mysteries.html). He even includes a chart!

My initial comment was “Michael Bourgon sets XACT_ABORT” but that’s actually not one of the list, so I had to change it for the sake of correctness even though I think it was funnier in its original guise.

Leave a Comment

Red Flags in Query Design

Published 2026-07-09 by Kevin Feasel

Thomas Williams has a list:

Nowadays I look after 3rd-party databases more than internally-developed ones, so I accept there’s a whole lot of ex-best practices, vendor preferences, and possibly shortcuts in queries I might come across – whether it’s a poorly-performing query, a blocker, or an error.

(Although, when I developed software more frequently, I was guilty of all the gripes below. My start in SQL, last century, was poring over a big yellow “For Dummies” book. I was the dummy.)

Click through for the list. I particularly hate tibbling, a rather derisive term for the malformed version of Hungarian notation. This would just lead me down a rant about how systems Hungarian notation was a mess, whereas apps Hungarian notation can be useful in certain circumstances. Tibbling provides no semantically valuable information, which is why I dislike it so much.

Leave a Comment

T-SQL Code Smells

Published 2026-07-09 by Kevin Feasel

Rob Farley has a few:

I feel like I should preface this with a disclaimer. I added “potentially-” to the title, because there are many queries that might seem bad but can actually perform just fine. There are queries that on the surface can be great, but are nasty without a particular index, and there are queries that make me cringe a little when looking at them, but are actually okay. Brent Ozar is asking about signs of bad code for this month’s T-SQL Tuesday (the 200th – and I have a response for all 200 if you look back through my history of posts), and he wants us to write this for 2004 Brent, rather than 2026 Brent.

Click through for what Rob has come up with. I agree with all of Rob’s examples and do appreciate his usage of the APPLY operator as a way of solving one common problem.

Leave a Comment

Traits of Sketchy Queries

Published 2026-07-09 by Kevin Feasel

Louis Davidson has a list of red flags in code:

I still feel like garbage, so I decided just a simple list would do. I will also preface this by saying each item could include “without a coherent comment.”

Everything on this list fills me with dread unless I read someone say: “Such and such was needed because the optimizer wouldn’t….” and then I at least know why they believed needed it.

Click through for Louis’s list. Most of these aren’t bad things per se, but they do serve as signs of a potential deeper issue.

Leave a Comment

Red Flags in Database Code

Published 2026-07-08 by Kevin Feasel

Tom Zika has a list and starts with AI-generated code:

This one didn’t exist three years ago. Now it’s the first thing I look for.

To be clear, I’m not anti-AI. If the AI wrote clean code, I probably wouldn’t even notice. The red flag isn’t that AI generated it – it’s the patterns that give it away. I recently saw a real case where someone needed to update a set of values. Simple enough, right? Here’s what the AI-generated solution did:

Click through for a laugh, as well as several other red flags.

Leave a Comment