Local and Global Temporary Tables

There are three ways to create temporary tables:

  1. create table #temp ( mycol int );
  2. create table ##temp ( mycol int );
  3. create table tempdb.dbo.temp ( mycol int );

What is the difference between those three techniques?

  1. Using the single hash symbol (#temp) will create a local temporary table that is visible only to the current session. This temporary table will be dropped automatically when it goes out of scope, or when the session is closed.
  2. Using the double hash symbol (##temp) will create a global temporary table that is visible to all users. This temporary table must be dropped explicitly; otherwise, it will be dropped during the next server reboot.
  3. Using the database/schema name (tempdb.dbo.temp) is similar to #2: will create a global temporary table that is visible to all users. This temporary table must be dropped explicitly; otherwise, it will be dropped during the next server reboot. However, the hash-named table (##temp) is not the same as the explicit table (tempdb.dbo.temp), in the same session or across sessions.

There is rarely, if ever, a need to create a global temporary table. If you find yourself doing this, your technique is probably incorrect. When using a global temporary table, there is no guarantee that another user is not using the same temporary table, and there is no protection against concurrency. These should never be used.

To see this in action, open a connection to SQL server and run the following code:

create table #temp ( mycol int );
create table ##temp ( mycol int );
create table tempdb.dbo.temp ( mycol int )

Then, open a second connection to the same SQL server and run the same code. The first line (#temp) will succeed; the second and third line will report “There is already an object named ‘##temp’ in the database” and “There is already an object named ‘temp’ in the database” respectively. This illustrates the session-independence of local temp tables (#temp), and the global exposure and name uniqueness of global temp tables (##temp and tempdb.dbo.temp).

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.