Temporary Tables – Part 2

Today, I am going to talk about local and global temporary tables.

A local temporary table is defined by using the CREATE TABLE syntax with the name of the table prefixed by one number sign #. The scope of the local temporary table is the current session or SPID.

A global temporary table is defined by using the CREATE TABLE syntax with the name of the table prefixed by two number signs ##. The table is visible to all sessions. The scope of the global temporary table is the current session or the last session that dereferences the table.

Both local and global tables are stored in the tempdb database. Since multiple process might be calling a stored procedure with a local temporary table, SQL Server post fixes the name of the table with a unique tag to keep them unique.

I am going to use the car ownership table for person A to demonstration how to work with local temporary tables. Please see the tiny AUTOS database I created in earlier article.

The code snipet below creates a local temporary table with scope related to my SSMS query window.

I am going to use an INSERT statment to populate the table with sample data.

To really make sure the data is there, I am going to use a SELECT statement to view the contents of the table.

Temporary tables would not be very useful if we can not perform UPDATE and DELETE operations on the records. The code below records the third record from the table, updates the 5 record with new data, and shows the results.

Last but not least, it is just good practice to clean up after yourself. The code below looks for the
table in tempdb. If it exists, it drops the table.

I will be using the Adventure Works Sample database supplied by Microsoft during the rest of this talk.

A common query pattern is to get the most recent id by date. Joining these results to the original table
will bring back the most recent record by date.

The SELECT query below stores the last sales order by customer id as a local temporary table called #MC. The main query joins to this table to return customer name, sales id, sub-total and order date. This might be a query for the SALES team to cold call customers that have not ordered in the past x months.

The code snipets above that drop tables have one fatal bug. If the code is executed from two query windows at the same time, one window will fail since it will try to drop a table that does not exist. One fix to this bug is to add the SPID to the end of the table name. Since each session (SPID) is now unique at a given time, the correct result will come back from the EXISTS query.

In short, local and global temporary tables have been around for a long time. Since older versions of the SQL Server engine do not support constructs such as table variables or common table expressions, it is wise to know how to use this technique. Next time, I will talk about table variables.

Related posts

One Thought to “Temporary Tables – Part 2”

  1. I truly enjoy reading on this site, it has wonderful articles . “Words are, of course, the most powerful drug used by mankind.” by Rudyard Kipling.

Leave a Comment