SQL Server Brain Basher of the Week #013 - Global Temporary Tables

Though we create temporary tables frequently for manipulating temporary data, global temporary tables are infrequently created. As you know, local temporary table is limited to the current session, and global temporary table is for all available sessions. Local temporary table will be automatically dropped when your session is disconnected and global temporary table will be remained until last connection that has referenced the table is disconnected. Now the question for the week is;

Global Temporary Table is global for the database or for the instance?

When this question is asked during my classes/workshops, most common answer for this is, it is global to the database. However, it is not the correct answer, It is global to the entire instance. Once a global temporary table is created, it can be accessed, modified and deleted by any user in any database hosted in the same instance.

Here is a sample code for that;

-- Connect with AdventureWorks database
USE AdventureWorks2014;
GO

-- Create global temporary table
CREATE TABLE ##TestTable
(
 Id int PRIMARY KEY
 , Name varchar(200)
);
GO

-- Query the table in the same session
SELECT * FROM ##TestTable;

Here is the result of SELECT.

Here is the result when accessing the table in a different session.

And here we are accessing the table from a different database. It works without any issue.