Nic Cox used to be a Data Warehouse and ETL developer here at OptimalBI. Nic used to regularly battle it out whenever we did an exercise challenge. He has now spread his wings and is now living in London where is enjoying life on the Kiwi OE. You can read the original post of this blog at Nic Cox Business Intelligence here.
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.
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,
We will be running regular guest posts from Nic. You can read his blog, SQL Server Series: What is an Index? or visit his full site here. You can also read Nic’s blogs from his time with OptimalBI here.
OptimalBI run regular Data Vault course for business analysts, data architects, and business intelligence developers in Wellington and Auckland. Find out more here