T-SQL Tuesday #201 – Temp Data

This month for T-SQL Tuesday, Jeff Taylor asks us to write about temp tables and temp data. Click the T-SQL Tuesday post logo to read the full invite.

Jeff provides half a dozen prompts to get the ball rolling, and I thought I’d discuss my thoughts on “the whole family. #temp vs. table variables vs. CTEs vs. indexed views. When does each one earn a spot?”

Indexed Views

I’ll start with what I’ve used the least: indexed views. One could argue they’re less about temp data and more about materializing data for repeated work. Like an index, it’s going to be affected by related updates, deletes, etc., so it’s not something you want where there’s a lot of activity going on. I recall this being considered for helping with queries involving a log table amongst other tables, which would have been a bad idea due to the heavy write activity.

Table Variables

Table variables haven’t been something I’ve used much. They can make sense for smaller workloads where the lack of statistics isn’t as much of a concern, but I’ve never been in a position where switching to a table variable from a temp table made a meaningful difference. Technically they have their place, but they’re not something I’ve dealt with often.

Common Table Expressions (CTEs)

Now we come to Common Table Expressions (CTEs). If I were in an interview and asked, “What’s one of your weaknesses?” I could probably plead guilty to using CTEs more often than I should. But hear me out. If I’m doing more of a one-off task, CTEs are simple enough for me to write, grab the data, send it off to who needs it, and move on to the next task. This is more acceptable when it’s not something that needs to be materialized and reused later. And even when I do use them, I’m not a monster that stacks CTE on top of CTE on top of CTE. If it comes down to something that’s going in a stored procedure or some other type of repeatable task, I’m looking more closely at temp tables instead of CTEs.

Temp Tables

Finally, temp tables. Temp tables are my choice when I need to materialize data and be able to utilize indexes and statistics. But that doesn’t mean they should be the default answer to every problem. If I’m copying millions of rows into a temp table just to query it once for a few rows (or in Jeff’s example, one row), then I should consider other options. Don’t treat temp tables as the sledgehammer approach and use them everywhere just because.

Keeping Your Options Open

None of the four can be the one true answer to all of your problems. You can’t really do a clear 1-4, best-to-worst, ranking, because it depends on your data and context. Pick the right tool for the job.

Thanks for reading!