Temporary tables in Sql Server

Temporary tables in Sql Server

Temporary tables , are very similar to the permanent tables. Permanent tables get created in the database you specify, and remain in the database permanently, until you delete(drop)them.On the other hand, temporary tables get created in the TempDB and are automatically deleted, when they are no longer used.

Different types of temporary tables:

1. Local temporary tables
2. Global temporary tables.

Now we create the temporary table..........................

use test

create table #EmpTable

(

Id int,

Name varchar(50)

)

select * from #EmpTable

insert into #EmpTable values(1,'kiran')

insert into #EmpTable values(2,'kk')

insert into #EmpTable values(3,'sp')

Check if the local temprory tables is created :-

 Temporary tables are created in the TempDB. Query the subobjects system table in TempDB. The name of the table, is suffixed with the lot of underscores and a random number. For this reason you have to use the LIKE Operator in the query

--when we create the # use in front that table then it create in TempDB

--then go in system database then TempDB in tempprary tables

Ex-:   select name from tempdb..sysobjects where name like '#EmpTable%'

When we create the temporary table in end of table name behind some random number take in sql server like this:-

If we want to name of table then use query:-

select name from tempdb..sysobjects where name like '#EmpTable'

output:  null  because each temp table  have  name with some random number

Note:- A local temporary table is available, only for the connection that has created the table.

Now we want use the query the one window like 2^nd  query window first connection if we want to open the new window like 2^nd query window second connection. In case first connection window query we can use the 2^nd connection query window if we use then show this error like that:-

Msg 208, Level 16, State 0, Line 1

Invalid object name '#personltbles'.

A local temporary table is automatically dropped, when the connection that has created the it, is closed.

Note:- now when we closed the query window connection then temporary table automatically drop and refresh the system database table table automatically deleted.

If the user want to explicitly drop the temporary table, he can do so using

DROP TABLE # EmpTable'

LOCAL Temprory procedure:If the temprory table , created inside the stored procedure, it’s gets dropped automatically upon the completion of stored procedure execution.

Create the stored procedure:-

---create stored procedure…………………..

create procedure splocaltmptable

as

begin

create table #personaldetail

(

Id int,

name varchar(20)

)

insert into #personaldetail values (1,'pukki')

insert into #personaldetail values (2,'sukki')

insert into #personaldetail values (1,'tukki')

select * from #personaldetail

end

--now execute the procedure

execute  splocaltmptable

it retrieve the value as output.

Note:- now when we execute the stored procedure then it will retrieve the value of output but we use 

select * from #personaldetail

this select query execute then select data automatically deleted.

Then show this error like this:-

Msg 208, Level 16, State 0, Line 1

Invalid object name '#personaldetail'.

Its temporary table in stored procedure automatically immediately  destroyed when we execute the stored procedure

 Now it is also possible for different connections , to create a local temporary table with the same name.

For example user1 and user2, both can create the a local temporary table with the same name  #personaldetail.

In case the same one query window connection and second window query connection using the create the table and any procedure  with same structure its possible and its provide the temporary table random number its basically different randomly.

GLOBAL Temporary table:- To create a global temporary table prefix the name of the table with 2 pound (##) symbols.

Syntax:-  --global temporary table

create table ##employeedetails

(

Id int,

name varchar(20),

)

--then refresh table in system database

 insert into ##employeedetails values(1,'kiran')

insert into ##employeedetails values(2,'kk')

insert into ##employeedetails values(3,'sp')

select * from ##employeedetails

--now if we select the table of another window then it retrieved data successfully

We use select data from any window connection

--but we can't create the table another with the same name.

It will show error like this:-

Msg 2714, Level 16, State 6, Line 1

There is already an object named '##employeedetails' in the database.

--In ##table not show the any random number

Global temporary table are visible to all the connections of the sql server, and are only destroyed when the last connection referencing the table is closed.

Multiple users , across multiple connections can have local temporary tables with the same name, but the global temporary table name has to be unique, and if you inspect the name of the global temp table, in the objecct explorer . there will be no random numbers suffixed at the end of the table name. 

Difference B/W local and Global Temporary tables

1. Local Temp Tables are prefixed with single pound(#)symbol, where as Global temp tables are prefixed with 2 pound(##)symbols.
2. Sql server appends some random numbers at the end of the local temp table name, where this is nit done for global tempo table names.
3. Local temporary tables are only visibleto that session of the sql server which has created it, where as global temporary tables are visibles to all thesql server sessions.
4. Local temporary tables are automatically dropped , when the session that created the temporary tables is closed, where as global temporary tables are destroyed when the last connection that is referencing the global temp table is closed.