Local and Global temporary table in SQL SERVER

Question

What is the difference between Local and Global temporary table in sql server

Answer 1

I find this explanation quite clear (it's pure copy from the help files):

Local temporary tables are visible only 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 the user disconnects from the instance of SQL Server. 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.

Answer 2

Quoting from Books Online:

Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

• A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.
• All other local temporary tables are dropped automatically at the end of the current session.
• Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

Answer 3

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