Click here to Skip to main content
11,703,632 members (69,980 online)
Click here to Skip to main content

Quick Overview: Temporary Tables in SQL Server 2005

, 22 Sep 2009 CPOL 1M 94
Rate this:
Please Sign up or sign in to vote.
Basic overview of temporary Tables in SQL Server 2005

Table of Contents

Introduction

SQL Server provides the concept of temporary table which helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside tempdb database.

In this article, I am just going to give a quick overview for beginners on those temporary tables. Please give your valuable suggestions and feedback to improve this article.  

Different Types of Temporary Tables

SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:

  • Local Temp Table
  • Global Temp Table

Local Temp Table

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

Global Temp Table

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

Creating Temporary Table in SQL Server 2005

As I have already discussed, there are two types of temporary tables available. Here I am going to describe each of them.

Local Temporary Table

The syntax given below is used to create a local Temp table in SQL Server 2005:

CREATE TABLE #LocalTempTable(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:

insert into #LocalTempTable values ( 1, 'Abhijit','India');

Now select records from that table:

select * from #LocalTempTable

After execution of all these statements, if you close the query window and again execute "Insert" or "Select" Command, it will throw the following error:

Msg 208, Level 16, State 0, Line 1
Invalid object name '#LocalTempTable'.

This is because the scope of Local Temporary table is only bounded with the current connection of current user.

Global Temporary Table

The scope of Global temporary table is the same for the entire user for a particular connection. We need to put "##" with the name of Global temporary tables. Below is the syntax for creating a Global Temporary Table:  

CREATE TABLE ##NewGlobalTempTable(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:

insert into ##NewGlobalTempTable values ( 1, 'Abhijit','India');

Now select records from that table:

select * from ##NewGlobalTempTable

Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.

Storage Location of Temporary Table

Temporary tables are stored inside the Temporary Folder of tempdb. Whenever we create a temporary table, it goes to Temporary folder of tempdb database.

TempTableLocation.JPG

Now, if we deeply look into the name of Local Temporary table names, a 'dash' is associated with each and every table name along with an ID. Have a look at the image below:

LocalTable.JPG - Click to enlarge image

SQL server does all this automatically, we do not need to worry about this; we need to only use the table name.  

When to Use Temporary Tables?

Below are the scenarios where we can use temporary tables:

  • When we are doing large number of row manipulation in stored procedures.
  • This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.
  • When we are having a complex join operation.

Points to Remember Before Using Temporary Tables

  • Temporary table created on tempdb of SQL Server. This is a separate database. So, this is an additional overhead and can causes performance issues.
  • Number of rows and columns need to be as minimum as needed.
  • Tables need to be deleted when they are done with their work.

Alternative Approach: Table Variable

Alternative of Temporary table is the Table variable which can do all kinds of operations that we can perform in Temp table. Below is the syntax for using Table variable.

Declare @TempTableVariable TABLE(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

The below scripts are used to insert and read the records for Tablevariables:

insert into @TempTableVariable values ( 1, 'Abhijit','India');

Now select records from that tablevariable:

select * from @TempTableVariable

When to Use Table Variable Over Temp Table

Tablevariable is always useful for less data. If the result set returns a large number of records, we need to go for temp table.

Reference and Further Study 

History

  • 23rd September, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Abhijit Jana
Technical Lead
India India
.NET Consultant | Former Microsoft MVP - ASP.NET | CodeProject MVP, Mentor, Insiders| Technology Evangelist | Author | Speaker | Geek | Blogger | Husband

Blog : http://abhijitjana.net
Web Site : http://dailydotnettips.com
Twitter : @AbhijitJana
My Kinect Book : Kinect for Windows SDK Programming Guide

You may also be interested in...

Comments and Discussions

 
Questioncopy Pin
Member 1117526123-Aug-15 19:40
memberMember 1117526123-Aug-15 19:40 
AnswerRe: copy Pin
Abhijit Jana23-Aug-15 20:49
mentorAbhijit Jana23-Aug-15 20:49 
GeneralGood Article Pin
Alireza_136223-Jul-15 20:37
memberAlireza_136223-Jul-15 20:37 
QuestionGood Article...!!! Pin
Bhavikkumar Chaudhari13-May-15 20:31
memberBhavikkumar Chaudhari13-May-15 20:31 
QuestionHi Pin
Member 1168723813-May-15 0:52
memberMember 1168723813-May-15 0:52 
GeneralMy vote of 2 Pin
saad fouad17-Feb-15 19:22
membersaad fouad17-Feb-15 19:22 
QuestionTemporary Tables in SQL Server 2005 Pin
Member 1116942921-Oct-14 1:52
memberMember 1116942921-Oct-14 1:52 
GeneralMy vote of 5 Pin
RUs12313-Oct-14 1:21
memberRUs12313-Oct-14 1:21 
Generalnice explanation Pin
Member 110594564-Sep-14 10:26
memberMember 110594564-Sep-14 10:26 
Questiontemp table as Cache Pin
jcpc913-May-14 18:24
memberjcpc913-May-14 18:24 
GeneralGood stuff!! Pin
John McCullough18-Apr-14 10:05
memberJohn McCullough18-Apr-14 10:05 
Generalinformative Pin
Amey K Bhatkar9-Apr-14 1:01
professionalAmey K Bhatkar9-Apr-14 1:01 
QuestionThanks.. Pin
Ravit119-Feb-14 16:56
memberRavit119-Feb-14 16:56 
GeneralThanks Pin
JSG mukesh12-Feb-14 20:26
memberJSG mukesh12-Feb-14 20:26 
GeneralMy vote of 5 Pin
Renju Vinod11-Dec-13 17:14
professionalRenju Vinod11-Dec-13 17:14 
QuestionUse of Table variable over temp table Pin
mishvivek5-Sep-13 9:01
membermishvivek5-Sep-13 9:01 
SuggestionSuggestion for Scope of Globel temp Table Pin
MODI_RAHUL29-Aug-13 2:33
memberMODI_RAHUL29-Aug-13 2:33 
QuestionNice explained Pin
jagadeesh.y29-Jul-13 4:12
memberjagadeesh.y29-Jul-13 4:12 
GeneralMy vote of 5 Pin
neetesh12326-Jun-13 20:30
memberneetesh12326-Jun-13 20:30 
GeneralMy vote of 5 Pin
Member 1005074224-Jun-13 20:23
memberMember 1005074224-Jun-13 20:23 
GeneralMy vote of 5 Pin
Sandeep Ramani22-May-13 1:26
memberSandeep Ramani22-May-13 1:26 
GeneralMy vote of 5 Pin
NIKHIL788 Agrawal29-Apr-13 23:33
memberNIKHIL788 Agrawal29-Apr-13 23:33 
GeneralMy vote 5 Pin
PratimaG15-Apr-13 4:50
memberPratimaG15-Apr-13 4:50 
GeneralMy vote of 5 Pin
csharpbd11-Apr-13 0:57
membercsharpbd11-Apr-13 0:57 
QuestionWHAT IS THE DIFFERENCE Pin
Thillairaja20-Mar-13 3:40
memberThillairaja20-Mar-13 3:40 
GeneralMy vote of 5 Pin
Rajendra.b23-Dec-12 17:19
memberRajendra.b23-Dec-12 17:19 
QuestionThanks Pin
CarticSQL27-Nov-12 23:23
memberCarticSQL27-Nov-12 23:23 
GeneralMy vote of 4 Pin
Prasad Guduri27-Nov-12 1:14
memberPrasad Guduri27-Nov-12 1:14 
QuestionTemp Table Pin
Prabhu.Palaniappan12-Oct-12 20:33
memberPrabhu.Palaniappan12-Oct-12 20:33 
GeneralMy vote of 4 Pin
NaveenWin26-Sep-12 20:31
memberNaveenWin26-Sep-12 20:31 
Questionarticle Pin
sivakarthiki3-Jul-12 7:08
membersivakarthiki3-Jul-12 7:08 
GeneralMy vote of 5 Pin
pratapvarun1-Jun-12 9:33
memberpratapvarun1-Jun-12 9:33 
GeneralFantastic Pin
Mr.Sourav.Maitra27-May-12 6:27
memberMr.Sourav.Maitra27-May-12 6:27 
GeneralMy vote of 5 Pin
Naresh Goradara21-May-12 23:17
memberNaresh Goradara21-May-12 23:17 
QuestionGood article Pin
pfsze22-Mar-12 4:52
memberpfsze22-Mar-12 4:52 
Just for a reminder, some database developers think the sql will clean up the temp. tables by itself so they never drop the temp tables. That is not right. Keep in mind, you create a temp table and you must drop it after you finish your work. Don't just leave it on memorry.
Stefan Sze

GeneralMy vote of 5 Pin
Vivek.Bhosale@synechron.com12-Mar-12 22:48
memberVivek.Bhosale@synechron.com12-Mar-12 22:48 
QuestionGood Article Pin
Deiveegaraja.A5-Mar-12 3:05
memberDeiveegaraja.A5-Mar-12 3:05 
GeneralMy vote of 5 Pin
sachin10d30-Sep-11 20:27
groupsachin10d30-Sep-11 20:27 
Questionhi Pin
narender.n3-Aug-11 1:51
membernarender.n3-Aug-11 1:51 
GeneralMy vote of 5 Pin
Sandeep Ramani10-Apr-11 21:40
memberSandeep Ramani10-Apr-11 21:40 
GeneralMy vote of 4 Pin
Ramanji mca16-Dec-10 7:44
memberRamanji mca16-Dec-10 7:44 
GeneralMy vote of 2 Pin
yharik.a6-May-10 22:09
memberyharik.a6-May-10 22:09 
GeneralRe: My vote of 2 Pin
Abhijit Jana12-Sep-10 18:12
mvpAbhijit Jana12-Sep-10 18:12 
GeneralGreat Explanations Pin
thatraja15-Jan-10 21:37
memberthatraja15-Jan-10 21:37 
GeneralRe: Great Explanations Pin
Abhijit Jana16-Jan-10 6:43
mvpAbhijit Jana16-Jan-10 6:43 
GeneralGood Pin
venkatakumard11-Nov-09 2:26
membervenkatakumard11-Nov-09 2:26 
GeneralRe: Good Pin
Abhijit Jana11-Nov-09 5:08
mvpAbhijit Jana11-Nov-09 5:08 
GeneralGood Pin
Md. Marufuzzaman25-Sep-09 19:02
groupMd. Marufuzzaman25-Sep-09 19:02 
GeneralRe: Good Pin
Abhijit Jana26-Sep-09 0:44
mvpAbhijit Jana26-Sep-09 0:44 
GeneralNice article Champ!! Pin
Brij24-Sep-09 4:07
memberBrij24-Sep-09 4:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150819.1 | Last Updated 22 Sep 2009
Article Copyright 2009 by Abhijit Jana
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid