Press "Enter" to skip to content

Category: T-SQL Tuesday

Temp Tables and Stored Procedures

Published 2026-08-12 by Kevin Feasel

Shane O’Neill breaks a stored procedure:

Ages ago, I broke one of the stored procedures in Brent Ozar‘s First Responder Kit.

I filed that feat under the heading “Cool; Good to Know“, and then promptly forgot about it. Part of me thinks that I forgot about it because I didn’t understand how I accomplished it. While another part is sure I forgot about it cause “good to know” wasn’t on the list of tasks with looming deadlines.

I’m a man of many parts, but of more deadlines. Now, let’s understand it together!

Click through to see how. Also, when Shane refers to finding Wally, we in North America know him as Waldo. That’s how hard to find he is: he changes his name depending on the continent.

Comments closed

Temp Tables as Frenemies

Published 2026-08-12 by Kevin Feasel

Steve Jones shares some thoughts on temporary tables:

My answer to Jeff’s question of temp tables as friend or foe is yes.

They are friends.

They are foes.

Most things that we struggle with in database work are tradeoffs. We have to balance the demands. It’s why we say “it depends” so often because we have to find a way to do more of one thing, while accepting less of another.

This reminds me of a line I heard from a computer science professor I had in school. His line was that computer engineers get miracles every other day—they get some new breakthrough in hardware that makes things faster, cheaper, smaller, etc. But in computer science, we only ever got one miracle: when you want to access a piece of information, the most likely next piece of information you want is near to the piece you accessed. That’s why we can work with memory in pages and how caching can be successful. Everything else in computer science is a trade-off.

Comments closed

The Nuances of Using Temp Tables

Published 2026-08-11 by Kevin Feasel

Rob Farley explains a position:

Let me say for starters that I’m pleased we have temporary tables. They’re tremendously useful, but often get abused. Compared to obvious villains like NOLOCK, cursors, and scalar functions, they’re really quite inane (but any of these features can be used for good – trust me). But temporary tables were the one of these I mentioned in last month’s post about signs of a bad query. If nothing else, using temporary tables probably means you’re writing procedural code rather than set-based queries.

The scenario I want to talk about is using temporary tables to materialise a set of data, ahead of using it in a separate query a moment later. That’s essentially what Jeff Taylor is asking us to comment on.

I agree very strongly with Rob’s position. In stored procedures, temp tables should be there for a specific reason, a resignation that it is a second- or third-best design decision. But sometimes, you need second-best because first-best doesn’t perform well enough.

Comments closed

Thoughts on Temp Tables

Published 2026-08-11 by Kevin Feasel

Andy Brownsword shares an opinion:

For August’s T-SQL Tuesday ask, Jeff has thrown temp tables to the wolves. Does the community like or loathe their usage?

I’ll predict a few ‘it depends’ responses, and I lean towards Jeff’s position that they’re more of the exception than the rule. I’d argue there’s one particular scenario where this exception becomes exceptional (heh).

I imagine that this topic will get a bit of play this month and that it will be a bit more contentious than some might expect. I do like Andy’s scenario of when temp tables become quite valuable.

Comments closed

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.

Comments closed

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.

Comments closed

Going Non-Clustered Index-Mad

Published 2026-07-17 by Kevin Feasel

Jeff Mlakar is saying the number of NCIs is too darn high!:

I wish I could say I was exaggerating when I say that I’ve investigated query performance involving 20+ NCI on a single table on more than a few occasions. “Why not?” you may think – the more NCI the better, right? Well think on this:

Click through for the reasoning. You know it’s extra-bad when 95% of those indexes have “dta” in the name. Jeff even calls that out in the post.

Comments closed

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.

Comments closed

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.

Comments closed