Ramblings of a SQL Hillbilly

Or, the mad ravings of a data warehouse forklift operator

Temporary Query Items: Scope

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

Last time we took a look at what a temporary query item is, and why it is useful. In this post, I’ll be looking at how Scope plays into the similarities and differences among Temporary Query Items. An understanding of scope and how it relates to each TQI is crucial to being able to choose the best one for your situation, which is why I’ve dedicated an entire post to it.

What is Scope?

Wikipedia has a great article about scope from a generic computer science standpoint. My “quick and dirty” definition of scope is “The Time and Place of validity for a temporary item.” It’s a decent definition, and it tells us that we’re concerned with two things: When and Where.


The Temporary part of Temporary Query Items tells us that they only exist or are only accessible for a limited period of time. This isn’t to say that there’s a time limit on the existence of any of the TQIs in the traditional sense of the word – on the contrary, in all cases the data will hang around at least until the end of execution of the query you’re using it in. Not all TQIs are created equal in this respect, though – some of them are only valid for a single use, others are valid until manually dropped or until the connection is closed, and one even survives reboots. Likewise, the actual data contained within the TQI may or may not change over time based on how the underlying data changes. In a way, that’s a whole nother aspect of scope – can the data get stale?


The Where aspect of Temporary Query Items is mostly related to your ability to reuse TQIs across multiple queries, executions, or connections. Some TQIs can only be used in one query, while others can be used multiple times during a single execution. Others can be used universally within the database or server.

Moving on

This was a relatively short post. I believe for simplicity’s sake that I’ll leave a discussion of how these aspects of scope play out in each TQI for posts dedicated to each TQI. Before we move on to our first TQI, however, we need to talk a little about indexing and statistics.