Types of SQL Server Temporary Tables
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.
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
- Correlated Subqueries
- Derived Tables
- Table Variables
- Common Table Expressions (CTE)
- Permanent Temporary Data Tables
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
About the author
View all my tips