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

Kenneth Fisher, 2015-03-16

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.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, System Databases, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, system databases, T-SQL, temp tables

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads