Introduction to SQL Server Temporary Tables - Reasons and Types

By: Rick Dobson

Overview

By a temporary data store, for this tutorial, it means some data that is not in a permanent data table.  The are various reasons why a temporary data store is advantageous over a permanent table which we will cover in this tutorial. We will also look at how using staging tables maintained for an extended period is useful.  One reason for maintaining staging tables for an extended period is that the table is one in a series that provides progressively more complete versions of the full set of data to be migrated at the end of a data migration project.

Reasons for Temporary Data Stores in SQL Server

There are multiple reasons for creating and using temporary data stores.  Some typical reasons are itemized below.

• You need to populate one or more tables with input data from a non-tabular data source or set of sources.  The original non-tabular data can be staged to a table of string values before extracting, transforming, and loading the source data into SQL Server tables.
• You need to develop reports based on a very large database for a one-off project.  The reports need access to only a small portion of one or more permanent data sources.
• A query needs access to a small subset of some permanent data store with fewer columns and/or rows than in the permanent data store.  The extraction query is reasonably complex and needs successive rounds of refinement for either design and/or substantive reasons.  In this kind of situation, a temporary data store may enhance performance over the successive rounds of testing on the way to developing the final query.
• You may sometimes create a temporary data store in a code block, such as a stored procedure or some dynamic SQL, but you also want to share that temporary data outside the block in which it was created.  You may use different types of temporary data stores depending on the scope of your data sharing requirements.
• You need a data store that is inherently for a discrete period, such as at the end of a shift, after trading closes for the day, or the end of a fiscal month.  This kind of data store is temporary in the sense that it is repopulated with values for each new shift, trading day, or fiscal month.
• A solution needs access to intermediate computed values.  In fact, there may be several rounds or layers of intermediate computed values that ultimately support the computation of a final set of computed values.  Only the final set of computed values are for saving on an ongoing basis.

Types of Temporary SQL Server Data Stores

Just as there are different reasons for creating temporary data stores, there are also different types of temporary data stores.  Some examples of temporary data stores are briefly described below. 

This tutorial is not designed to drill down extensively into any one type of temporary data store.  Instead, the tutorial aims to provide a broad overview of different types of temporary data stores.  The level of coverage aims to help you get started using them and equips you to make your own evaluations about which works best for your set of requirements.

• A temp table is a real database table in a permanent database.  All temp tables reside in the tempdb database, which is a system database.  A temp table is temporary in that it is generally no longer available when the database connection for creating a temp table no longer exists.
• Subqueries are select statements nested inside of other SQL statements or clauses of SQL statements.  There are many different variations for subqueries, and these variations can affect the application for a subquery.
• A derived table is a special kind of subquery inside of the from clause of a select statement.  This type of subquery is code that returns a result set that acts as a table in many respects.
• A table variable has some properties like local variables and other properties like tables.  You define a table variable with a declare statement.  Its scope is the batch, stored procedure, or function in which the table declaration resides.
• A common table expression (CTE) is a result set returned by a select statement.  It is temporary in the sense that its scope is limited to the trailing select, insert, delete, or update statement.  CTEs are often used to simplify complex queries.  Also, CTEs are sometimes used for recursive queries – namely, for queries that call themselves.
• Another strategy for storing temporary data is in a permanent table.  With this approach, you populate a table in a user database, then operate on it as required, and finally drop the permanent table when the temporary data it stores is no longer needed, such as at the end of an application.

Additional Information

The balance of this tutorial will focus on different types of temporary data stores and how they can be used in SQL Server.  However, you may find the following links good resources now or after you go through the individual parts of the tutorial.

• Temporary Tables in SQL Server
• INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
• Performance Comparison – Tables, Temp Tables, and Table Variables
• Table Variables In T-SQL
• How to use SQL Server CTEs to make your T-SQL code readable by humans
• Differences Among CTE, Derived Table, Temp Table, Sub Query, And Temp Variable