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.
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.
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.
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!