Day: August 13, 2026

Louis Davidson shares an approach:

Temp tables fit into my query writing process as one of those last ditch efforts to make a query execute fast enough. Of course it would have been harder if I had followed all the rules and added a lot of test cases, but I had a pretty busy week last week and am finishing this pretty late on Tuesday night, so I just gave my opinions.

I like Louis’s strategy. It’s easy to add a lot of complexity to queries out of habit, to micro-optimize performance, or because of a meandering thought process. But many times, taking a step back to think about what could make a query simpler will be helpful.

One thing I’ll cover that Louis didn’t touch on is that performance level is (or should be) a requirement. If you have a query running millions of times a day on a system, then yes, it makes sense to squeeze out every microsecond. But for an ELT job that finishes in 20 minutes and where you have a 6-hour window to get the data loaded, shaving five minutes off of the query’s runtime isn’t that important, especially if you’re in read-committed snapshot isolation or using another form of optimistic concurrency.

This is why I recommend starting with simple and only moving to more complex solutions when you need them. Now, do I always follow my own advice? Err…well, the sign pointing to Boston doesn’t have to go there itself…

Leave a Comment

Deborah Melkin covers the gamut:

My stance is that temp tables are a useful tool when properly used, but they’re very easy to misuse to the point of creating problems. Table variables are even more misused for similar reasons; that could be a whole other blog post that almost starts and ends with “they’re not variables stored in memory but just regular temp tables without stats.”

Click through for examples in which temp tables have been quite helpful, and cases in which temp tables have been actively harmful.

Leave a Comment

Marlon Ribunal compares two techniques:

For this post, I needed to do some research because there is more to #temp tables than simply creating one and using it. As I started digging into the topic, I realized there are a lot of discussions around #temp tables and @table variables, especially around when one might be a better choice over the other.

One of the common arguments is that #temp tables go to disk while @table variables stay in memory. Another argument is that this is a myth. That got me curious about what the actual differences are and when they really matter. Discussions like this almost always starts with where the data is stored (disk vs memory).

Yeah, the “table variables are just in memory” is a myth, at least for normal table variables. Memory-valued user-defined table types do change things, as they don’t use tempdb. But they come with their own set of handcuffs.

Leave a Comment