SQL: Global temporary tables are almost never the answer in SQL Server

I was doing some consulting recently and reviewing a developer’s code. I was surprised to find extensive use of global temporary tables. Let me start by saying this: global temporary tables are almost never what you should be using.

When I asked why he’d used them, he told me that he wanted the temporary tables that he created in his procedure, to be available to other procedures that were run from within the procedure. So in PROC-A, he was running PROC-B and PROC-C. A temporary table was created in PROC-A and he wanted to be able to use it in PROC-B and PROC-C.

Local vs Global Temporary Tables

Local temporary tables (i.e. tables with names starting with a single #) are available where they are created, and in any other procedures run from within that same scope. You don’t need a global temporary table to do that.

Local temporary tables are dropped when they go out of scope (i.e. the session or context where they were created is dropped). And of course you can manually drop them with a DROP statement as well.

Ideally, you’d always drop them in your code rather than waiting for them to go out of scope. tempdb is a shared resource and you don’t want it clogged up with unnecessary data for too long.

Global temporary tables (i.e. tables with names starting with ##) are visible to all users. These are dropped dropped when the session that created the table ends and all other tasks have stopped referencing them (ie. when other queries that are using them have completed). And yes, you can manually drop them with a DROP statement.

I’m not a fan of creating user objects in tempdb but if you really wanted a temporary table that stayed until the server restarts, one curious option would be to create a user table in tempdb automatically every time the system starts up. You could do that with either an Agent job (easiest) or by assigning a startup procedure (more reliable but also requires a configuration change).

Abstraction

One thing you need to decide early on though, is if temporary tables are the right way for you to pass data from one proc to another. They’re certainly easy, and importantly, the data doesn’t get duplicated when the sub-procedure is called.

However, for me the jury is out on whether this is a good idea or not. What you end up doing is making the sub-procedure much harder to test. It ends up referring to a table that doesn’t exist anywhere in the procedure’s code. That just breaks all concepts of abstraction pretty solidly.

As I said though, people like it for performance. You aren’t copying the data.

An option to consider instead is creating a real table and putting the temporary data there, keyed off your session ID. Then the only issue is how it gets emptied again. That wouldn’t have an automatic clean-up, and that’s important if you have people creating temporary data and never removing it themselves; just letting it go out of scope.

Dropping and Recreating

Before creating temporary tables, it’s common to make sure they don’t already exist. In the past, you’d often see this type of code:

IF OBJECT_ID('tempdb..#SourceData') IS NOT NULL
BEGIN
    DROP TABLE #SourceData;
END;

CREATE TABLE #SourceData
(

Now you can just use:

DROP TABLE IF EXISTS #SourceData;

CREATE TABLE #SourceData
(

NOTE: When you create these tables, you don’t provide a schema name. In fact if you do, it’s ignored.

When are global temp tables needed or useful ?

I almost never use them. However, they do have uses. For example, if I wanted to “snoop” on the data that’s in a temporary table in another session, I could use a global temporary table for that.

I’ve seen them used for licensing schemes i.e. counting the number of active sessions. But nowadays, if you can, an in-memory non-durable table would probably work better.

In most cases that I see them used, a “real” table would actually be a better solution.

2021-02-04