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


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')
SELECT object_id('tempdb.dbo.##GlobalTempTable')

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'))
SELECT object_name(object_id('tempdb.dbo.##GlobalTempTable'),DB_ID('tempdb'))

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.

One thought on “There are #temp tables and then there are ##temp tables.

  1. […] in every session. That’s the idea, right? It’s one of the things that differentiates a global temp table from a local temp table. But there can be some difficulties with […]

Leave a Reply

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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

Join 3,755 other subscribers

Follow me on Twitter

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