Esat Erkec
Listing temporary tables in tempdb database

How to drop temp tables in SQL Server

March 23, 2020 by

Temporary tables, also known as temp tables, are widely used by the database administrators and developers. However, it may be necessary to drop the temp table before creating it. It is a common practice to check whether the temporary table exists or not exists. So, we can eliminate the “There is already an object named ‘#temptablename’ in the database” error during the temporary table creation.

Temporary Tables

The temporary tables are used to store data for an amount of time in SQL Server. Many features of the temporary tables are similar to the persisted tables. Such as, we can create indexes, statistics, and constraints for these tables like we do for persisted tables.

The types of temporary tables affect the life-cycle of the temporary tables. Now, we will take a glance at them.

Types of the Temporary Tables

Local Temporary Tables: The name of this type of temporary table starts with a single “#” hashtag symbol, and they are solely visible on the created session. If the session which has created the local temporary table is closed, the temporary table will be dropped automatically by SQL Server.

The following query will create a local temporary table:

How to create a local temporary table

Global Temporary Tables: The name of this type of temporary table starts with a double “##” hashtag symbol and can be accessed from all other connections. This is the major difference between the local and global temporary tables. If the session where the global temporary table was created is closed, the global temporary table will be dropped automatically.

The following query will create a global temporary table:

How to create a global temporary table

The following table expresses the main differences between global and local temporary tables:

Local Temporary Tables

Global Temporary Tables

Names start with a single “#” hashtag symbol.

Names start with a double “##” hashtag symbol.

Tables can be accessed only from the session where the table was created.

Tables can be accessed from all other sessions.

Cannot be dropped by the other connections.

Can be dropped by the other connections.

Where are the Temporary Tables stored?

When we create a temporary table, they are created in the tempdb database. After creating a local temporary table, if we check the temporary tables folder in tempdb, we will see a weird table name. On the other hand, global temporary tables are created with their original names.

Listing temporary tables in tempdb database

SQL Server adds random numbers at the end of the local table variables names. The idea behind this logic is pretty simple. More than one different connection can create local temporary tables with the same name, so SQL Server automatically adds a random number at the end of this type of temporary table name. In this way, the SQL Server avoids the same name conflicts.

How to Drop Temp Tables

There is no doubt that after these learnings, if we want to drop any temp table, we should work on the tempdb database.

How to drop Temp Tables?

As the best practices before creating a temporary table, we should check the existence of this temporary table. In this way, we don’t experience the following error:

Drop a Temp Table

To achieve this check, we can use different techniques. Let us learn these techniques:

Using OBJECT_ID function to check temporary table existence

OBJECT_ID function is used to obtain the identification number of the database object. OBJECT_ID function can take the object’s name as a parameter so we can use this function to check the existence of any object in the particular database.

The following query will check the #LocalCustomer table existence in the tempdb database, and if it exists, it will be dropped.

For the local temporary tables:

For the global temporary tables:

Using sys.tables table to check temporary table existence

In this method, we will check the existence of the temporary table with the help of the sys.tables because this table returns user tables in the relevant database.

For the local temporary tables:

For the global temporary tables:

As we can see, we check the existence of the #LocalCustomer table in the tempdb database, and if it exists, we have to drop it. At this point, we need to underline one issue, the table name is searched with the LIKE operator, and we also added the wildcard character at the end of the temp table name. As we stated, local temp tables are created with random suffix so that we can not know the exact name of them.

Using DROP TABLE IF EXISTS statement

This is the last technique on how to drop a temp table, which we will learn. DROP TABLE IF EXISTS statement checks the existence of the table, and if the table exists, it drops. We have to underline one point about this statement; it works on SQL Server 2016 or the higher version of the SQL Server. In the following query, DROP TABLE IF EXISTS statement, we will check the #LocalCustomer table existence, and if it exists, it will be dropped.

For the local temporary tables:

For the global temporary tables:

In the following table, we can see all the methods that we have mentioned briefly:

How to drop temporary tables

Using OBJECT_ID function

Using sys.tables

Using

DROP TABLE IF EXISTS statement

Conclusion

In this article, we learned the basics of the temporary tables, and we discussed dropping the temp table techniques in SQL Server. According to my thought, the best way is using the DROP TABLE IF EXISTS statement, but we can use other alternative methods easily.

Esat Erkec
Latest posts by Esat Erkec (see all)
281 Views