Simple • Straightforward

So easy a Caveman
Programmer can do it.

›› Let's Get Started

Temporary Tables

Not Your Regular Tables

Temporary (temp) tables are a SQL programmer's best friend. These provide a simple way to store larger amounts of data in a more feasible way then a regular variable. There are 3 different kinds of temp tables:

  1. Temp Table (#)
  2. Temp Table Variable (@)
  3. Global Temp Table (##)

Similar to a regular database table, these different types of temp tables all store data in a table format (rows and columns). But, they each have a specific use. Let's review each type and their intended uses.



Temp Table (#)

This is the main type of table when people generally talk about temp tables. It can be instantiated in 2 different ways:

Option 1
CREATE TABLE #MyTempTable1 (Col1 VARCHAR(50), Col2 VARCHAR(50))

Option 2
SELECT Col1, Col2
INTO #MyTempTable1
FROM ItemInventory

Notice on the second option, we didn't previously define the table. You can select the data directly into the table and the columns will be created with the same formats from the table you are selecting from.

Once the table is created in either option, it is used similar to a regular table (select, insert, delete, etc.). You can also create indexes on the table as well. The temp table and any indexes created will be dropped in 2 ways.

  1. When the connection drops that the temp table was created in.
  2. When you run the following command: DROP TABLE #MyTempTable1

Using a temp table (#) over a temp table variable (@) is good when you have more than 1,000 rows you are need to work with, or if you just don't want to define all of the columns yourself :).

Note: The temp table (#) is stored in the temp database on the hard drive when it is created, but it will be loaded into memory as much as possible.



Temp Table Variable (@)

This is similiar in use as other variables in the system and it is dropped automatically at the end of the procedure. It is not recommended to use this type of temp table if there are a lot of rows involved since non-clustered indexes cannot be created on it. You can, however, create primary and unique restraints on the table, but this might look a bit funky compared to how a normal table operates. You instantiate the temp table as follows:

DECLARE @MyTempTable1 TABLE (Col1 VARCHAR(50), Col2 VARCHAR(50))



Global Temp Table (##)

This type of temp table is similar in every aspect to a regular temp table (#), except that you can use it across multiple connections. But what does this mean precisely?

Imagine you have a stored procedure that contains a regular temp table (#). When you declare and use that table, it is only contained and accessible inside the stored procedure. A global temp table (##) on the other hand, can be used outside of the stored procedure and will not de-allocate itself until one of the following conditions are met:

  1. All connections using the table have been dropped (ie: if 1 procedure creates it, but another one starts using it, the table will continue to exist until both procedures finish)
  2. You run the following command: DROP TABLE ##MyTempTable1

Most people will tell you to never use global temp tables because it can get hazardous if you have 2 tables with similar names being used. While I would use the same caution, I have found that these can be handy if you are importing large amounts of data on a recurring basis (ie: a daily import into the database), and you need multiple procedures to all review the same large dataset.





Comments

Name:
Title:   
Commenting Currently Disabled


Nahaz Nizam
9/11/2012 2:18:08 AM



thats gud
gh
9/11/2012 2:17:12 AM
hgh


ghgh
Stevie
8/4/2011 2:34:37 PM



Check that off the list of things I was cnofuesd about.
Jason
7/16/2011 7:20:26 PM
Fourth kind, kind of


Hi Arto - I like your comment, but I think it might be a bit abstract for the first time reader as they go through all of these articles. I do want to keep things as simple as possible for folks who are just learning about optimization. Hopefully you understand. I think I might be missing what you are referencing when asking to revise some of the titles in the comment fields. I would be happy to make some clarifications if you could point out an example or two.
Arto Ahlstedt
7/13/2011 4:10:19 PM
Fourth kind, kind of


Sometimes the query engine needs to create a work table in processing a query. They are created in tempdb. These work tables are not directly usable but they are real materialized temporary tables which consume disk space, nevertheless. (Hence the answer 4 in my test :) ). (Jason, please consider revising some of the Title fields in some comments; its meaning was not obvious to all of us. Erase this parenthesized expression too while you're at it.)
Jason
7/4/2011 11:10:04 AM
Temp Variables


Gail - My bubble has been busted! Thanks for the info. I read up on the Microsoft post as well and have updated that section of the page.
Gail Shaw
7/3/2011 6:31:32 PM



The table variable being memory only is unfortunately a very prevalent and persistent myth. Table variables and temp tables are both stored in tempDB, kept in memory as much as possible, spilt to disk when necessary. See the first section of this: http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/
Jason
7/3/2011 3:16:23 PM
Gloabl Temp Table Alternative


That is a great idea Ian. Thanks for posting it!
Ian Oberst
6/30/2011 12:28:56 PM
Products Analyst


A nice alternative to using a global temp table in something like a daily import is creating a local temp table in a parent procedure. Any procedure called by the parent will have access to the temp table. In this way we can use the functionality of a global table without having to worry about other connections possibly accessing/modifying the data in the table.