Global Temporary tables are defined on creation by adding the double pound sign (##) as a prefix. Unlike temporary tables they are available to all SQL Server sessions until the table is destroyed/the creating session goes out of scope.
Global Temporary tables are similar to regular temporary tables in that they exist in tempdb and cause less locking and logging than permanent tables. To boost query performance when working with small data sets (under 1000 rows) it is advisable to use table variables rather than temporary tables since table variables run within server memory and are not created in tempdb. See my article covering this for further details.
Temporary tables in general are useful when needing to work with data on a temporary basis. This is especially useful when analyzing data before writing a production level query. Although using temporary tables for short term storage may seem convenient, it is usually best practice to convert these into a single query using joins or sub-tables for performance reasons.
I usually do not use Global Temporary tables, but they do come in handy in some special instances. For example if I am running tests on a complex or long running stored procedure and want to access data set at a particular point in the procedure from a query in another tab or instance of SQL Server Management Server (SSMS), then a Global Temporary table can be useful.
Here is an example of creating a Global Temporary table:
CREATE TABLE ##TmpTbl(
id INT,
name VARCHAR(32)
)
INSERT INTO ##TmpTbl (id,name) VALUES (1,'test')
SELECT id,name FROM ##TmpTbl
DROP TABLE ##TmpTbl
So to sum up: Global Temporary tables behave very much like Local Temporary tables except that their scope is greater, spanning all SQL Server sessions. In general Global Temporary tables are not widely used, but they can be useful if you need to create and populate a work table.
References:
Justin Cooney 
1 thought on “SQL Server: Global Temporary Tables”