Local Temp Tables and Global Temp Tables

Have you ever noticed a temp table with not one but two pound signs in front? Let’s take a look at the differences between ##Temp and #Temp.

Shop Local

You’ve probably seen a local temp table before. This is the type of temp table created with one # in front of the table name:

CREATE TABLE #LocalTemp (
	Id INT
	,DisplayName NVARCHAR(40)
	);

This temp table only exists for the current session. When you’re finished with the table, you can remove it yourself or let it be removed by SQL Server when the current session is ended.

Going Global

A temp table can also be created with two # in front which makes the temp table a global temp table:

CREATE TABLE ##GlobalTemp (
	Id INT
	,DisplayName NVARCHAR(40)
	);

Temp tables created this way can be available to other sessions for access. They are also removed when all sessions using them have been ended.

Access Example

Let’s say we created #LocalTemp and ##GlobalTemp while using the StackOverFlow2013 database. We can expand the following path in SSMS and confirm their existence:

Or we can search tempdb with this query

SELECT name
FROM tempdb.sys.tables
WHERE name LIKE '#%';
GO

Let’s add a few rows of test data to our temp tables:

INSERT INTO #LocalTemp
SELECT TOP 10 Id, DisplayName
FROM Users;
GO

INSERT INTO ##GlobalTemp
SELECT TOP 10 Id, DisplayName
FROM Users;
GO

Now, let’s switch our session over to a different database and see what happens when we try to access the tables.

USE ExampleDB;
GO
SELECT * FROM #LocalTemp;
GO
SELECT * FROM ##GlobalTemp;
GO

We’re still in the same session and have no problems accessing the data.

Let’s open a new query window and try the global temp table:

USE ExampleDB;
GO
SELECT * FROM ##GlobalTemp;
GO

All good. If we try the local temp table:

USE ExampleDB;
GO
SELECT * FROM #LocalTemp;
GO

We do not have access.

Thanks for reading and enjoy the holidays!