Checking for temporary table existence

Database Code Smells

I was recently reviewing a newly created T-SQL stored procedure. This procedure was verifying temporary table existence with the following code:

Seeing this takes me back to one of my favorite presentations, where I compare Temporary Tables and Table Variables. In this, I go over several of the methods that I have seen for how code found on the internet actually does this task… and I show why they are all doing it wrong.

Let’s start by looking at a few other examples that I’ve seen on the internet to check for temporary table existence:

The code that I found is similar to the first two methods above – except that this newly created procedure is using the ancient (SQL 2000) sysobjects view instead of the more modern sys.objects view. While that doesn’t make this usage wrong by itself, it is enough to reject the code in a code review. Read on for why this just doesn’t work to verify temporary table existence.

So, what’s wrong with these methods?

What’s wrong with these methods is that they don’t consider the scope of a temporary table. Books Online states:

Local temporary tables are visible only in the current session

So, let’s test out the above statements with the scope of the temporary table in mind.

Let’s start by creating a local temporary table:

Now, in a different connection (session), run any of the above statements.

When you run the first two statements (and remember, these are like the one in the stored procedure), what you get is the result back from the select statement. This would indicate that the temporary table does exist.

When you run the third statement, it errors out.

Invalid object name ‘#TestTable’.

While the fourth statement doesn’t return a result set. This just returns:

Commands completed successfully.

Explain this please

Since the temporary table and the statements are in different connections, the temporary table isn’t in scope to the connection running the statements.

In the first two statements, the system views are queried to see if a row exists where the name starts with the name of the temporary table. This is because a local temporary table does not keep the name assigned… there is a suffix appended to it. Let’s look at just the stored name:

Which returns:

We can see that the name uses underscores to pad it out, followed by a sequential hexadecimal sequence. For local temporary tables, the name is always 128 characters long. My assumption is that this is so that the suffix is always in the same spot, making some kind of optimization possible. However, the point is that every temporary table has more to the name than what you specified. Temporary tables can be used inside stored procedures, which could be called from many connections at the same time. This means that SQL Server needs a way to differentiate all the different tables from each other, and to associate it with the proper connection / session that created it. The suffix handles this differentiation.

What the above two queries are actually doing is checking to see if there is any temporary table that starts with this name present. It’s not restricted to the current session. And it’s not really checking out the name properly either. Have you ever seen several temporary tables created along the line of #Temp1, #Temp2, etc.? The above code would find an entry for #TestTable1 also. To search for only #TestTable, it would have to include at least one underscore in the comparison. But that still doesn’t handle that the query can return tables that aren’t visible to the current connection.

What about the other statements?

Okay, let’s go over the third and fourth statements. The third statement generated an error because it tried to run a select statement against the temporary table. This table is not in scope for this session.

The fourth statement generated no result for two reasons. The first is that the statement wasn’t scoped to the tempdb database. The current database did not have an object in it starting with the name of the temporary table, so the if statement’s true condition wasn’t run. However, even if tempdb is the current database, the statement still would not have generated a result… because the query is looking for the exact table name, not the name with something after it (the underscores and hex identifier).

The fifth statement used to work. However, in SQL Server 2012, temporary tables are now created with negative object_id values. I think that you can figure out why that code won’t work in SQL Server 2012+.

So, what is the proper way to check for temporary table existence?

After showing these ways that don’t work, I need to leave you with the only method (that I know of) that does work. That method utilizes the OBJECT_ID function:

This method takes into account the scoped temporary table, and gets its object_id from tempdb. If it exists, you will get a non-null value. A null value means that it doesn’t exist. Even the remarks section of the OBJECT_ID function shows using this method.

Short, simple, but most importantly… it works correctly.