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:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE #LocalCustomer ( CustomerId int, CustomerName varchar(50), CustomerAdress varchar(150) ) GO INSERT INTO #LocalCustomer VALUES(1,'Katelyn Montropx' ,'30 Crescent Avenue DRUMMUIR CASTLE') GO SELECT * FROM #LocalCustomer |
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:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE ##GlobalCustomer ( CustomerId int, CustomerName varchar(50), CustomerAdress varchar(150) ) GO INSERT INTO ##GlobalCustomer VALUES(1,'Adam Tottropx' ,'30 Mztom Street LONDON') GO SELECT * FROM ##GlobalCustomer |
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.
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.
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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
IF OBJECT_ID(N'tempdb..#LocalCustomer') IS NOT NULL BEGIN DROP TABLE #LocalCustomer END GO CREATE TABLE #LocalCustomer ( CustomerId int, CustomerName varchar(50), CustomerAdress varchar(150) ) |
For the global temporary tables:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
IF OBJECT_ID(N'tempdb..##GlobalCustomer') IS NOT NULL BEGIN DROP TABLE ##GlobalCustomer END GO CREATE TABLE ##GlobalCustomer ( CustomerId int, CustomerName varchar(50), CustomerAdress varchar(150) ) |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#LocalCustomer%') BEGIN DROP TABLE #LocalCustomer; END; CREATE TABLE #LocalCustomer ( CustomerId int, CustomerName varchar(50), CustomerAdress varchar(150) ) |
For the global temporary tables:
|
1 2 3 4 5 6 7 8 9 10 11 |
IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '##GlobalCustomer%') BEGIN DROP TABLE ##GlobalCustomer ; END; CREATE TABLE ##GlobalCustomer ( CustomerId int, CustomerName varchar(50), CustomerAdress varchar(150) ) |
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:
|
1 2 3 4 5 6 7 8 |
DROP TABLE IF EXISTS #LocalCustomer GO CREATE TABLE #LocalCustomer ( CustomerId int, CustomerName varchar(50), CustomerAdress varchar(150) ) |
For the global temporary tables:
|
1 2 3 4 5 6 7 8 |
DROP TABLE IF EXISTS ##GlobalCustomer GO CREATE TABLE ##GlobalCustomer ( CustomerId int, CustomerName varchar(50), CustomerAdress varchar(150) ) |
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.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023