Source: Pixabay

Temporary tables are aptly named. They are a table that is created for temporary use within a session or sessions. This blog looks at temp tables in SQL Server, what they are, what they allow you to do and how to create them. Please note that while table variables are mentioned in this blog due to similarities between the two, they are not covered in detail, but keep an eye out for them in a future blog.

What is a Temporary Table?

A temporary table is just like a normal table, except it is created and stored within tempdb. It is created almost like you would create any other table, and you can treat it like normal tables too. Commands you are able to perform against a temporary table include being able to drop it, alter it, create indexes on it and calculate its statistics, as well as the usual insert, update and delete. They are primarily used for temporary data storage such as in an intermediary step when loading or transforming data, with the idea being that you will not need to reference that data again other than at this point of the process.
There are two types of temporary tables, local and global. A local temp table is only available within your connection, no one else has access to it. A local temp table will be deleted when you close your connection or rollback your transaction.  If you create a temp table within a stored procedure, it is deleted at the completion of that procedure.  A local temp table is created using one single hash (#) symbol at the start of the table name within a create table statement.

CREATE TABLE #TempLocal
(
Id int not null,
Name varchar(20)
)

A global temp table is available across multiple connections, meaning other people or sessions are also able to access them. A global temp table will be deleted when all sessions that reference that table are closed. A global temp table is created using a similar syntax to a local temp table bar one minor difference, it is created using two hash (##) symbols at the start of the table name instead.

CREATE TABLE ##TempGlobal
(
Id int not null,
Name varchar(20)
)

Of course like normal tables, you can create your temp table and insert data into it in one step using the INTO command.

SELECT Id, Name
INTO #TempLocal
FROM Schema.Table

When to use a temporary table?

Temporary tables are most useful when you are dealing with large amounts of data that you cannot load from source to target in one query. You might be applying some complex business rules to some data within a stored procedure, and to do so you need an intermediary step along the way. Since you are able to calculate statistics and create indexes on a temp table, you can optimize it for querying as an intermediary step during your ETL processes. If you were working with small amounts of data that required little processing, then you would probably be better off using a table variable.

Wrap Up

Temporary tables act just like a normal table would, except it only exists for the duration of your connection if it is a local temp table, or for the duration of all associated connections if it is a global temp table.  They provide you with a stepping stone solution during the loading of data and are most advantageous with larger data sets.  That’s all for this SQL Server Series blog on temporary tables.
Until next time,
Nic