Temporary Tables in SQL

Posted by Harshitha under Sql Server on 8/31/2015 | Points: 10 | Views : 408 | Status : [Member] | Replies : 3
What is the difference between a Local Temporary Table and Global Temporary Table?

* A Local Temporary Table is created by giving it a prefix of # whereas a Global Temporary Table is created by giving it a prefix of ##.

* A Local Temporary Table cannot be shared among multiple users whereas a Global Temporary Table can be shared among multiple users.

* A Local Temporary Table is only available to the current DB connection for the current user and are cleared when the connection is closed whereas a Global Temporary Table is available to any connection once created. They are cleared when the last connection is closed.




Responses

Posted by: Sheonarayan on: 8/31/2015 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
Thanks for posting the question as well as answer Harshitha. Ideally this should have gone into Interview section of the website. Forums should have only question that needs answer from others.

Keep it up!


Regards,
Sheo Narayan
http://www.dotnetfunda.com

Harshitha, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Shreedar on: 8/31/2015 [Member] Starter | Points: 25

Up
0
Down
Hi Harshitha.

As Mr.Sheo Narayan said this forum section is for question and answer type.

If you want to share a good content, then you can write article and submit. If the content is of small length you can submit as a interview question.

Regards

Sridhar Thota.
www.dotnet-sridhar.blogspot.com

Harshitha, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Manub22 on: 9/22/2015 [Member] Starter | Points: 25

Up
0
Down
Both Temporary Tables and Global Temp Hash Tables are created on disk in the tempDB system database, like other permanent tables, but vanishes when SQL Server is restarted.

Temporary Table (#) name starts with single # followed by the given name and is suffixed with a session-specific ID so that it can be differentiated with other similar named tables created in other sessions. The name is limited to 116 chars. The scope of #Table is limited to its session, so other sessions cannot use this table.

Global Temporary Tables (##) operate much like Local Temporary Tables; they are also created in tempdb and cause less locking and logging than permanent tables. However, their scope is across sessions and are visible to all the sessions, until the creating session goes out of scope.

More on #Tables: http://sqlwithmanoj.com/tag/temporary-tables/

Harshitha, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response