如何在SQL Server中删除临时表

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.

临时表，也称为临时表，已被数据库管理员和开发人员广泛使用。 但是，可能有必要在创建临时表之前将其删除。 通常的做法是检查临时表是否存在。 因此，在临时表创建过程中，我们可以消除“数据库中已经有一个名为'#temptablename'的对象”错误。

临时表 (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.

临时表用于在SQL Server中存储数据一段时间。 临时表的许多功能与持久表相似。 例如，我们可以像为持久化表那样为这些表创建索引，统计信息和约束。

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.

本地临时表：此类临时表的名称以单个“＃”井号标签符号开头，并且仅在创建的会话上可见。 如果关闭了创建本地临时表的会话，则SQL Server将自动删除该临时表。

The following query will create a local temporary table:

以下查询将创建一个本地临时表：

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:

以下查询将创建一个全局临时表：

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.

当我们创建一个临时表时，它们是在tempdb数据库中创建的。 创建本地临时表后，如果我们检查tempdb中的临时表文件夹，我们将看到一个奇怪的表名。 另一方面，将使用其原始名称创建全局临时表。

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.

SQL Server在本地表变量名称的末尾添加随机数。 这种逻辑背后的想法非常简单。 多个不同的连接可以创建具有相同名称的本地临时表，因此SQL Server会在此类型的临时表名称的末尾自动添加一个随机数。 这样，SQL Server避免了相同的名称冲突。

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

毫无疑问，经过这些学习，如果我们想删除任何临时表，我们应该在tempdb数据库上工作。

如何删除临时表？ (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:

要实现此检查，我们可以使用不同的技术。 让我们学习以下技术：

使用OBJECT_ID函数检查临时表是否存在 (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.

OBJECT_ID函数用于获取数据库对象的标识号。 OBJECT_ID函数可以将对象的名称作为参数，因此我们可以使用此函数检查特定数据库中是否存在任何对象。

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

以下查询将检查tempdb数据库中是否存在#LocalCustomer表，如果存在，则将其删除。

For the local temporary tables:

对于本地临时表：

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:

对于全局临时表：

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)
)

使用sys.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.

在这种方法中，我们将借助sys.tables检查临时表的存在，因为该表返回相关数据库中的用户表。

For the local temporary tables:

对于本地临时表：

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:

对于全局临时表：

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.

如我们所见，我们检查tempdb数据库中#LocalCustomer表的存在，如果存在，则必须删除它。 在这一点上，我们需要强调一个问题，使用LIKE运算符搜索表名，并且在临时表名的末尾添加通配符。 如前所述，本地临时表是使用随机后缀创建的，因此我们无法知道它们的确切名称。

使用DROP TABLE IF EXISTS语句 (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.

这是关于如何删除临时表的最后一项技术，我们将学习。 DROP TABLE IF EXISTS语句检查表的存在，如果该表存在，则删除该表。 我们必须强调这一点。 它适用于SQL Server 2016或更高版本SQL Server。 在下面的查询DROP TABLE IF EXISTS语句中，我们将检查#LocalCustomer表是否存在，如果存在，则将其删除。

For the local temporary tables:

对于本地临时表：

DROP TABLE IF EXISTS  #LocalCustomer
GO
CREATE TABLE #LocalCustomer
(
 CustomerId int,
 CustomerName varchar(50), 
 CustomerAdress varchar(150)
)

For the global temporary tables:

对于全局临时表：

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	IF OBJECT_ID(N'tempdb..#TempTableName') IS NOT NULL BEGIN DROP TABLE #TempTableName END GO   CREATE TABLE #TempTableName ( Col1 VARCHAR(100) )
Using sys.tables
Using DROP TABLE IF EXISTS statement	DROP TABLE IF EXISTS  #TempTableName GO CREATE TABLE #TempTableName ( Col1 VARCHAR(100) )

如何删除临时表
使用OBJECT_ID函数
使用sys.tables	IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#TempTableName%') BEGIN    DROP TABLE #TempTableName; END;   CREATE TABLE #TempTableName ( Col1 VARCHAR(100) )
使用 DROP TABLE IF EXISTS语句

结论 (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.

在本文中，我们学习了临时表的基础，并讨论了在SQL Server中删除临时表的技术。 根据我的想法，最好的方法是使用DROP TABLE IF EXISTS语句，但是我们可以轻松地使用其他替代方法。

翻译自: https://www.sqlshack.com/how-to-drop-temp-tables-in-sql-server/