Wednesday 28 March 2012

Beginning SQL : Difference Between Local and Global temporary table in SQL SERVER

Local temporary tables are visible only in to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced.
Local temporary tables are deleted after disconnects from SQL Server instance .
Global temporary tables are visible to any user and any connection after they are created,
and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

You can create tables using following syntax.

Table Variable :
          DECLARE @tmp TABLE

Table variables are only visible to the the connection that creates it, are stored in RAM,
and are deleted after the batch or stored procedure ends.

Local temporary tables :
          CREATE TABLE #tmp

Local temporary tables are only visible to the connection that creates it,
and are deleted after the connection is closed.

Global temporary tables :
          CREATE TABLE ##tmp

Global temporary tables are visible to everyone, and are deleted after the connection that created it is closed.

Tempdb permanent tables :
          USE tempdb CREATE TABLE tmp

Tempdb permanent tables are visible to everyone, and are deleted when the server is restarted.

Local Table can not be shared between multiple users.
Global Table can be shared between multiple users.

Write in comment if you know more differences. I will add this in my blog.

2 comments:

  1. How about multiple temp DB's with the same name from different connections?

    ReplyDelete
  2. there also available @@ temp variable tables

    DECLARE @@tmp TABLE(ii INT)
    INSERT INTO @@tmp VALUES (8),(9)
    SELECT * FROM @@tmp

    ReplyDelete