Difference between local and global temporary tables:?

 Posted by Ddd on 3/26/2011 | Category: Sql Server Interview questions | Views: 29823 | Points: 40
Answer:

local:
1) denoted by # symbol.
2) valid for the current connection only.
They are cleared as soon as the curent connection closes.
3)cannot be shared between multiple users.

global:
1)denoted by ## symbol.
2)Available to all the connections once created.
They are cleared when the last connection is closed.
3)can be shared betwen multiple users.

Both of then are stored in the tempdb database


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: PandianS on: 3/26/2011 | Points: 10
Hi

Nice Explanation on both.

Small correction on Global Temp table(#2)

"They are unallocated from the Tempdb when the connection closed where the actual table was created" :)

But, not "Last connection is closed", because, It can be accessed from many connections / sessions.

Cheers
Posted by: M.yash2512 on: 3/26/2011 | Points: 10
hello pandianS,
would you please describe your post with some more clarification?
Posted by: LethalCookie on: 4/28/2011 | Points: 10
Global Temporary tables are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server
Posted by: PandianS on: 4/29/2011 | Points: 10
Hi

I would like to explain little better on "Global Temporary Table" for some better understanding... :)

1. Global Temp., table can be accessed by the all connections/sessions once created.

2. The Global Temp., will be dropped automatically when the source connection/session ended or The table dropped manually.

Session: 50
- I have created a Global Temp., Table named ##SQLFunda in this session 50

- This Global Temp., table can be accessed by the all connections/sessions ONLY till the source connection/session (Session #50 - where the table actually created) is closed or The table dropped manually from ahywhere (from any connection/session).

Cheers

Login to post response