Types of SQL Server Temporary Tables

By: Rick Dobson | Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | > Temp Tables

Problem

There are several reasons why you may need to store data in a temporary state. This could be for pre-processing steps, staging, data cleansing, etc. The temporary data can be either materialized data and actually stored in tables or just a temporary set of data that is created by sub-queries, common table expressions, table valued functions or other means. In this series of tips we will look at different ways of doing along with examples, but first we will cover some reasons for doing this and different types of temporary data stores.

Solution

Reasons for Temporary SQL Server Data Stores

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 SQL Server Temporary 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.

In this series we will cover the following:

Temporary Tables
Subqueries
Correlated Subqueries
Derived Tables
Table Variables
Common Table Expressions (CTE)
Permanent Temporary Data Tables

Next Steps

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.

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips