Local and Global Temporary Tables in SQL Server 2008

In this article I am sharing my experience with Temporary Tables in SQL Server 2008.

Description

Temporary Tables are quite similar to Permanent Tables in database; Permanent Tables are created in a specific database and persist until the database exists. Whilst Temporary Tables are created in the tempdb and are automatically deleted when they are no longer in use. Like shown below:



Types of Temporary Tables

• Local Temporary Tables
• Global Temporary Tables

Local Temporary Tables

Local temp tables are similar to Permanent Tables in SQL Server, it accepts the single hash value "#" as the prefix when created. Syntax: (CREATE TABLE #t). They are visible only to the connection that creates it, and are deleted when the connection is closed.

I've created a local temp table with the following syntax:

create table #Android

( ID int NOT Null ,Name nvarchar(50) ,CompanyName nvarchar (50))

And inserted few values into this table.
 

insert into  #Android values (1,'Galaxy S2','Samsung')

insert into  #Android values (1,'Galaxy S3','Samsung')

insert into  #Android values (1,'IPhone5','IPhone');

insert into  #Android values (1,'Blackberry Z10','Blackberry');

 

select * from #Android



There are a few characteristics of local Temporary Tables:

1. It starts with single hash value "#" as the prefix of the table name.

2. A Local Temporary Table is only for the connection in which it was created.

3. Each Local Temporary Table has a random value at the end of the table name as depicted in the following image:
   
   
   

4. A Local Temporary Table is automatically dropped when the existing connection is closed, or the user can explicitly drop it with the following command "drop table #Android".

5. If the Temporary Table is created in a Stored Procedure then it is automatically dropped on the completion of the Stored Procedure execution.

6. You can create a Local Temporary Table with the same name but in a different connection, and it is stored with the same name along with various random values.
   
   
   
   
    

Global Temporary Tables

Global Temporary Tables are also similar to Local Temporary Tables in SQL Server, except two "##" values are used as the prefix at the time of their creation. Syntax: (CREATE TABLE ##tablename). They are visible to all connections of SQLServer, and only destroyed when the last connection referencing the table is closed (in which we have created the Global Temporary Table).

create table ##MobileDetails

( ID int NOT Null ,MobileName nvarchar(50) ,CompanyName nvarchar (50))

 

insert into  ##MobileDetails values (1,'Galaxy S2','Samsung')

insert into  ##MobileDetails values (1,'Nokia Lumia','Nokia')

insert into  ##MobileDetails values (1,'IPhone5','IPhone');

insert into  ##MobileDetails values (1,'Blackberry Z10','Blackberry');

After executing the preceding command you will see the following structure in the object explorer as depicted in the following image:



Run the following line in a SQL Server query window and see the output:

select * from ##MobileDetails



There are a few characteristics of Global Temporary Tables:

1. It starts with the single hash value "##" as the prefix of the table name and its name is always unique. There is no random number appended to the name.
   
   
    

2. Global Temporary Tables are visible to all connections of SQL Server.

3. Global Temporary Tables are only destroyed when the last connection referencing the table is closed (in which we have created the Global Temporary Table).

4. You can access the Global Temporary Tables from all connections of SQL Server until the referencing connection is open.

Hope you like this demonstration.

Keep coding and Smile.