Temporary tables are a useful tool in SQL Server provided to allow for short term use of data. There are two types of temporary table in SQL Server, local and global.
Local temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed. Global temporary tables are available to any connection once created, and are dropped when the last connection using it is closed.
Both types of temporary tables are created in the system database tempdb.
Creating Temporary Tables
Temporary tables can be created like any table in SQL Server with a CREATE TABLE or SELECT..INTO statement. To make the table a local temporary table, you simply prefix the name with a (#). To make the table a global temporary table, prefix it with (##).
SELECTage AS DummyField1,
Both of these samples create a local temporary table named #myTempTable with two fields DummyField1 and DummyField2.
To make these into global temporary tables, just replace (#) with (##)
age AS DummyField1,
lastname AS DummyField2
Similarly, you can SELECT, INSERT, and UPDATE like any other table, but remember the prefix is part of the table name.
Deciding between Local and Global
When deciding which type of table to use ask yourself two questions. First, "Do I need this data to persist when I am done using it?" If so, I need a standard table, not a temporary table. Second, Do I need the data to be accessed outside of my single process?" This question can sometimes be a little tougher to figure out, so I have a simple suggestion. Make it a local temporary table for now, and if you find out you need a larger scope, change it later. In today's world there are plenty of tools to find and replace, so there's no reason to start with a global temporary table unless you know you will need it.
In the real world there are several uses for temporary tables. Many people find it easier to follow their work if they create a series of tables and select the data from them to get to their final result. Most of the time this will be slower than a few good joins, but not always. Besides, getting started you might find it easier to follow the process of gathering your data.
Another use for temporary tables is when you will have multiple users accessing your system and you need to keep track of temporary user information. An example you would probably be familiar with would be a shopping cart on a web application. You could collect a list of items people are purchasing, and if they choose to leave the site rather than checking out, the shopping cart will just go away. Using local temporary tables you eliminate the worry of customers seeing another user's shopping cart because their session has no access to it.
This has been a relatively high level overview of temporary tables in SQL Server. If you are looking for more in-depth information, do a search for SQL Server temporary tables with your favorite search engine.
Add your comments here.
You can also send feedback to firstname.lastname@example.org
Mithun Shankar - May 31, 2005 12:20 AM
The article is enough for beginners, but for advanced users more complicated examples are needed.
Josh Fruits - June 5, 2005 10:35 PM
As the title and subject indicate, this is just a high-level overview. Generally, those are for people unfamiliar with the topic being discussed. Check the temporary tables articles in Books Online if you are looking for more detailed information.
Sreekar Mankala - July 4, 2005 8:37 AM
I am so grateful to u for providing this new things
Nguyen Thi Linh - September 7, 2005 2:18 AM
I have created a stored procedure (SP) with creating and dropping a global temporary table. However, when I run this SP many times there is an error happing due to dropping global temporary table many times
Josh Fruits - September 8, 2005 3:36 PM
It sounds like you are having a specific problem with a stored procedure and temporary tables, not really related to this article. Please post the question with a few more details about what is going on and what type of errors you are seeing in the forums section of the site.
|» Domain name registration|
|» E commerce solutions|
|» Flash design websites|
|» Flash designing india|
|» Google ranking services|
|» Joomla website developers|
|» Mobile application development|
|» OS commerce website development|
|» php website development|
|» Search engine marketing|
|» Seo services india|
|» Software development india|
|» Web design services|
|» Web development india|
|» Website redesign services|
|» Wordpress website developers|
|» Magento website developers|
|» Search engine services|
|» Search engine optimization|
|» Freelance php developers|
|» Hire mobile developers|
|» Zen cart developers|
|» X cart developers|
|» Open cart developers|
|» Blog developers india|
|» Content management system|
|» Article submission india|
|» CRM development india|