Website Design United States, Website Design California, Website Designing United States, Website Designing California

What is a SQL Server Temporary Table?


Introduction

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 (##).

  • -- Create a local temporary table using CREATE TABLE
  • CREATE TABLE #myTempTable
    (
    DummyField1 INT,
    DummyField2 VARCHAR(20)
    )

    -- Create a local temporary table using SELECT..INTO

    SELECT

    age AS DummyField1,
    lastname AS DummyField2
    INTO #myTempTable
    FROM DummyTable

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 (##)

  • CREATE TABLE ##myTempTable
  • (
    DummyField1 INT,
    DummyField2 VARCHAR(20)
    )

    SELECT
    age AS DummyField1,
    lastname AS DummyField2
    INTO ##myTempTable
    FROM DummyTable

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.

Real World

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.

Conclusion

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.

Author Information:

Josh Fruits

http://www.programmers-corner.com

josh@programmerscorner.com
Comments:

Add your comments here.

Name

Comment

You can also send feedback to feedback@programmers-corner.com

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.



 















 


© 2008-2009 dotnet4all.com