Differentiate between local and global temporary tables in SQL

In SQL Server, temporary tables can be created in runtime and can do all sorts of things which can be achieved by a normal table. However, since these are temporary tables, their scope is limited. There are two types of temporary tables:

• Local temporary tables
• Global temporary tables

Both of these temporary tables are physical tables which are created within the Temporary Tables folder in tempdb database. Temporary tables are automatically dropped when they go out of scope, unless they are explicitly dropped using DROP TABLE.

• Local temporary tables are visible only in the current session, i.e; are only available to the current connection for the user.
• Local temporary tables are deleted or destroyed after the user disconnects from the instance of SQL Server.
• Local temporary tables are are automatically destroyed at the termination of the stored procedure or session that created them.
• Local temporary table name is prefixed with hash ("#") sign.
• Global temporary tables are visible to all sessions and all users.
• Global temporary tables are deleted or dropped when all users referencing the table disconnect from the instance of SQL Server.
• Global temporary tables are dropped automatically when the last session using the temporary table has completed.
• Global temporary table name is prefixed with a double hash ("##") sign.

Table creation statements

• Table variables (DECLARE @tmp_surgeon TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends.
• Local temporary tables (CREATE TABLE #tmp_surgeon) are visible only to the connection that creates it, and are deleted when the connection is closed.
• Global temporary tables (CREATE TABLE ##tmp_surgeon) are visible to everyone, and are deleted when all connections that have referenced them have closed.
• Tempdb permanent tables (USE tempdb CREATE TABLE tmp_surgeon) are visible to everyone, and are deleted when the server is restarted.

Local temporary table – Usage

CREATE TABLE #tmp_local (in_user_id int, vc_username varchar(50), vc_address varchar(150))

INSERT INTO #tmp_local VALUES (1, 'Rohan', 'India');

SELECT * FROM #tmp_local

After execution of all these statements, if you close the query window and again execute "INSERT" or "SELECT" Command, it will throw the following error:

Msg 208, Level 16, State 0, Line 1

Invalid object name '#tmp_local'.

Global temporary table – Usage

CREATE TABLE ##tmp_global (in_user_id int, vc_username varchar(50), vc_address varchar(150))

INSERT INTO ##tmp_global VALUES (1, 'Rohan', 'India');

SELECT * FROM ##tmp_global

Global temporary tables are visible to all SQL Server connections. Once you create a global temporary table, it becomes visible to all the users.