Local and Global Temporary tables in SqlServer 2008 ~ DotnetPiper

Tuesday, July 15, 2014

Local and Global Temporary tables in SqlServer 2008

Posted on 4:52 AM by Sachin Kalia

Local and Global Temporary tables in SqlServer 2008

In this article I am sharing my thoughts on Temporary tables in SQL Server 2008.

Description: Temporary tables are quite similar to permanent tables in database; permanent tables get created in the specific database and persist till the database exists. Whilst Temporary tables get created into the tempdb and automatically deleted 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 single hash value ‘#’ value as prefix at time of its creation .Syntax:- (CREATE TABLE #t) 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 few characteristics of local temporary tables:

1. It starts with single hash value ‘#” as prefix of table name.

2. Local temporary table is only for connection in which it has created.

3. Each Local temporary table has a random value at end of table name as depict in below image:

4. Local temporary table gets automatically dropped when existing connection is closed, or user can explicitly drop with the following command “drop table #Android”.

5. If the temporary table is created into the stored procedure, it gets automatically dropped on the completion of the stored procedure execution.

6. You can create local temporary table with the same name but in different connection, and it stores with same name along with different random values.

Global temporary tables: Global temp tables are also similar to local temporary table tables in SQL server, Excepts two ## value as prefix at time of its creation .Syntax:- (CREATE TABLE ##tablename) are visible all connection of SQLServer, and only destroyed when the last connection referencing the table is closed (in which we have created the Global Temp 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 above command you will see the following structure into the object explorere as depict below in image:

Run the following line into sqlserver query window and see the output:

select * from ##MobileDetails

There are few characteristics of global temporary tables:

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

2. Global temporary tables are visible all connection of SQLServer.

3. Global temporary tables are only destroyed when the last connection referencing the table is closed (in which we have created the Global Temp table).

4. You can access the Global temporary tables from all connection of SQLServer till the referencing connection is open.

Hope you like this demonstration:

To know more about MVC kindly go through with the links given below:

· Smart Working With Custom Value Providers in ASP.Net MVC

· Invoke Action With Model Binders in MVC

· Extension Helpers Method in MVC

· Custom Button With TagBuilder Using MVC Razor Engine

· Precompiled Razor View Using RazorGenerator MVC and PreCompiledViewEngine in MVC 4

· RETURN MULTIPLE MODELS IN SINGLE VIEW IN MVC3

· CALL CONTROLLER ACTION METHOD FROM JQUERY USING AJAX

· EXECUTION ORDER OF FILTERS IN MVC 4 WITH PRACTICES: IMPORTANT FAQ

· MEANING OF SCAFFOLDING IN MVC