There are #temp tables and then there are ##temp tables.

Leave a comment

March 16, 2015 by Kenneth Fisher

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,134 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: