Local and Global Temporary Tables in SQL Server

Introduction

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

Temporary Tables in SQL Server

Temporary Tables are quite similar to Permanent Tables in the database; Permanent Tables are created in a specific database and persist until the database exists. Temporary Tables are created in the tempdb and are automatically deleted when they are no longer in use. As 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.

Step 1 created a local temp table with the following syntax.

create table #Android

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

Step 2 inserted a 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 a 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.
   
   

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');

select * from ##MobileDetails