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

In previous posts, I’ve talked some about what Temporary Query Items are and why you might need them. I’ve also given an overview of scope and how it might affect your choices of TQIs for your own queries. Likewise, I’ve worked through indexing and statistics, a reasonable understanding of which can help to understand the advantagese and disadvantages of some TQIs. Now, I’d like to talk a bit about TempDB and memory, and how they can also affect your choice of Temporary Query Item.

Memory: Where The Party Is

Memory is the most critical hardware component when it comes to SQL Server. If you have more memory than you have data, then you won’t have to worry about disk latency (much.) Memory is incredibly fast compared to every other piece of hardware you have (excepting CPU caches, but those are generally out of your control.) You want more memory. You always want more memory. More RAM.

Right. So more RAM is mo’ betta. But we have a 2 terabyte data warehouse – that really doesn’t fit into RAM. And memory isn’t just for storing data like it is stored on disk – we also have to store data in the form that we’re querying. That means that we need a way to handle running out of memory for a query without crashing the whole system. What we have is TempDB.

TempDB: The Public Toilet of SQL Server

Brent Ozar describes TempDB as the public toilet of SQL Server. Everything that needs to temporarily dump to disk in SQL Server uses TempDB. This includes index rebuilds, the version store, and even innocent little SELECT statements.

TempDB is slow, even if you put it on SSDs. Memory is a TON faster. You could theoretically allocate a RAMDisk for TempDB, but…

Why Does It Matter?

Okay, so memory is fast, TempDB is slow, but TempDB is there when we run out of memory. What does that have to do with Temporary Query Items? Good question!

Some of our TQIs live in TempDB. Some of them don’t. What this means for you, is that your choice of Temporary Query Item might be influenced by how it uses or abuses TempDB.

Moving On

The only major concern that I haven’t touched on when it comes to the differences among Temporary Query Items is syntax. Each one has unique ways in which to invoke it, and I could theoretically dedicate a post just to that. However, you’ve been waiting for specifics, and specifics you shall have! I’ll cover syntax of each TQI individually as I come to them.

First up: temporary tables.