Category: T-SQL Tuesday

Jeff Taylor gives us the low-down:

A couple of weeks ago, I asked a simple question with a loaded answer: are temp tables a friend or a foe? The responses did not disappoint. They ran from full-throated defense to a measured “it depends,” and one of you built a lab. That’s what I was looking for.

If there is a consensus, it is this: the reflex is the problem, not the tool. Almost everyone agreed that dumping data into a #temptable out of habit is a mistake. Almost everyone also had a case where a temp table was the right answer, and sometimes the only one. So let’s get into it, in no particular order.

Click through for the cast of characters and what everyone came up with.

Comments closed

Andy Levy answers a question:

As with almost everything in SQL Server, there is no one-size-fits-all answer when it comes to temp tables. There are times where temp tables have rescued a query, and others where they’ve just caused trouble. And then there’s the times where you’re just scratching your head.

Can you use a CTE or a derived table in place of a temp table? In many cases, yes! But it doesn’t always work out well. When things get particularly complex, the SQL Server optimizer can struggle to find a good plan that doesn’t involve lots of table scans.

Read on for Andy’s thoughts.

Comments closed

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.

Comments closed

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…

Comments closed

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.

Comments closed