Note: This is part of a series on Temporary Query Items.

While working with one of my junior developers on a query one day, I discovered that she had inadvertently deleted a few records due to not understanding how a CTE maps back to the underlying data. I was able to quickly resolve the issue thanks to how our testing environment is set up, but it prompted me to create a presentation on what I call Temporary Query Items. What I was really looking for was different ways to use and reuse transformed, abbreviated, or combined data sets in queries. Turns out, in SQL Server we have plenty of options for that, depending on what your requirements are, and so I was able to put together a pretty good presentation on the upsides and downsides of each. In this series of posts, I’ll be exploring what I cover in my presentation. This post: An intro to Temporary Query Items.

But Why Male Models Temporary Query Items?

I’d like to clarify my terminology a little bit here. My original inclination was to call them Temporary Objects, but I wanted to be careful because some of them… well… aren’t. Objects in SQL Server are usually the kinds of things you can find in sys.objects, and some of my TQIs can’t be found there due to scoping – but that’s a Good Thing™ because it means that sys.objects doesn’t get all gunked up with stuff that’s transient anyway. And that’s where the Temporary part of the nomenclature comes in – everything I’m going to talk about is transient in some way. These are the sand castles and seasonal flowers of T-SQL, the stuff that doesn’t need to live long. Every last one of them is meant to support Queries, and only to support queries.

And as for Items? Well, I’ve already told you that these aren’t all Objects. I thought about Fluff, except that title doesn’t really convey how useful they are. They aren’t really Clauses, and they don’t fit too well as Sub-Queries or Statements. So Items they are.

Who Are You Calling Temporary?

While researching potential candidates for my list, I found five things that I would call useful for some form of transient use while querying. I’ll go more in-depth on these in later blog posts.

• Temporary Tables
• Table Variables
• Views
• Common Table Expressions (aka CTEs)
• Derived Tables

But Why Male Models Temporary Query Items?

Okay, so now we know what a TQI is, and it’s somewhat clear WHY they’re called TQIs. But what do we need them for? Why even have them?

Reuse, Reduce, Recycle

One of the most useful attributes of a TQI is data reuse in a query. Let’s say that you’ve got two related tables that you want to join together and pull some columns from. Now let’s say also that you’ll want to take the resulting data set, and join it to itself three times. Without a TQI, you’re looking at a six-table join, with multiple join conditions repeated over and over. With one of the Temporary Query Items, you can cut the amount of code you’re working with down quite a bit – six joins becomes three or four joins, depending on the approach used. I’ll cover how this works in particular in posts for each of the TQIs.

Alternatively, maybe you have multiple different queries, all doing some of the same things. With a TQI, you can wrap parts of those queries into one easily-referenced item that you can keep using without having to duplicate the code everywhere.

Code Simplification With Limited Duplication Duration

That title is a mouthful, so I’ll break this down. Let’s say that you have a query that gets run quite often, building a customized view of a dataset (whether you’re transforming the data, limiting columns, limiting rows, or even combing tables.) It’s a query that performs pretty well, and it’s really not worth duplicating the data in a permanent form such as a table. A TQI could be just the ticket for making things easier on your users or your automated process.

Consistent View with Automated Cleanup

Again, all TQIs are transient. Most of them come with some automated form of self-cleanup, meaning that you avoid the issue of forgetting to drop a table that you only needed for a bit.

Location, Location, Location

Most of these TQIs live in memory, although some can live partially in tempdb. This could help reduce the impact of disk i/o (ask Brent Ozar, Disk I/O is Bad.)

Getting On With It

“Okay Mister Slacker, you’ve piqued my interest – bring on the Temporary Tables!” I love your enthusiasm! But, we’re not quite ready for those details yet – first, we have to talk about scope.