Temporary tables are a common enough thing in T-SQL that most of us have probably used them on a fairly regular basis. What I don’t think a lot of people know is that there are two types of temporary tables.

• Local Temporary Tables
  SELECT * INTO #LocalTempTable FROM sys.databases
• Global Temporary Tables
  SELECT * INTO ##GlobalTempTable FROM sys.databases

 
They are really pretty much the same. The only major difference is scope.

A local temporary table (#) is scoped to the current connection only. That means it can cross batches. (If you don’t know what I mean by a batch you can read what I wrote about GO the batch separator.) It also means that when you close the session the temporary table is dropped. You can manually drop a temp table but unless there is a need during your code you don’t have to.

So what’s different about a global temporary table? Well first of all (as you probably saw above) you use two #s when you create it. The Global part references the fact that scope is much broader. Any session can call a global temporary table even if it’s connected using a different login than the one that originally created the table. Global temporary tables are still dropped automatically (temporary remember?) but not until all tasks have stopped referencing it and the original session has been closed.

Now I did find something interesting while looking at temp tables. Remember that all temp tables are stored in TempDB. That means we can find the object_id for them like this:

-- Create temp tables first
SELECT * INTO #LocalTempTable FROM sys.databases
SELECT * INTO ##GlobalTempTable FROM sys.databases

SELECT object_id('tempdb.dbo.#LocalTempTable')
UNION ALL 
SELECT object_id('tempdb.dbo.##GlobalTempTable')

-1434584985
309576141

Every single time I tried it the local temporary table had a negative object ID and the global temporary table had a positive one. I’m not really sure if there is any significance there but I’ve only ever seen negative object IDs on system objects before.

Next let’s reverse the process and get the object name for the those object_ids.

SELECT object_name(object_id('tempdb.dbo.#LocalTempTable'),DB_ID('tempdb'))
UNION ALL
SELECT object_name(object_id('tempdb.dbo.##GlobalTempTable'),DB_ID('tempdb'))

#LocalTempTable_____________________________________________________________________________________________________000000000008
##GlobalTempTable

Notice that ##GlobalTempTable is named normally, #LocalTempTable on the other hand has a rather obvious extension with a number at the end. This is fairly logical since any number of sessions can have the same temporary table, all stored in TempDB, and if they had the same name there would be a problem.

Oh and quick note, all of the above queries were done from the same session. If you create the temp tables then run the object_id/object_name queries from a different session you will get NULL for the #LocalTempTable, while ##GlobalTempTable will work the same.

One little editorial comment at the end here. As with almost all things SQL Server, temp tables can be overused. They are very useful tools but if they are the only tool you use you can easily cause yourself some grief.