SQL server

Temporary tables introduction.

Temporary tables are those tables that are intended for temporary use and are not required to be durable. They are stored in the tempdb. There are two types of temporary tables: local and global. Local temporary tables are created with a single hash (#) preceding the table name and global temporary tables have two ##.

Local temporary table data are only accessible to the session that created the table, whereas global temporary table data can be accessed by other sessions. Let’s create a local and global temp table and see how they behave.

I opened up and new query (let’s call this session A) in SSMS and created a local and global temp table respectively:

create table ##temp_global_test(    — Global temporary table
id int,
update_time datetime)

create table #temp_local_test(    — Local temporary table
id int,
update_time datetime)

Now let us insert some data:

insert into ##temp_global_test values(1,current_timestamp)
insert into #temp_local_test values(1,current_timestamp)
go 5

In the current session, session A, run:

select * from ##temp_global_test
select * from #temp_local_test

Now let’s  open up a new query in SSMS without closing session A. Let’s call this new session as session B. In session B run:

select * from ##temp_global_test
select * from #temp_local_test

You will get an error for the second command stating .. Invalid object name ‘#temp_local_test’. This is because the temp table #temp_local_test is local to session A and cannot be queried in session B. ##temp_global_test is a global temp table and can be accessed by both session A and session B. This is the difference between local and global sessions.

Do not close session A or session B. If you expand SSMS>Server\instance>Databases>System Databases>tempdb>Temporary Tables, you will see under the name column: ##temp_global_test and #temp_local_test_____… If you close session B first, you will still see both these tables. If you close session A first, then both these tables are lost even if you had a open session B referencing the global temp table.

Advertisements
Standard

Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s