如何在SQL Server中删除表变量？我应该这样做吗？

Question

我在一个脚本中有一个表变量（不是存储过程）。两个问题：

1. 如何删除表变量？Drop Table @varName会出现“语法错误”的错误。
2. 我应该总是这样做吗？我听说这是一种好习惯。对于这样的小脚本，它真的有必要吗？

这是我的代码：

Declare @projectList table(
    name varchar(40) NOT NULL);

Insert Into @projectList
Values ('BCR-00021')

Select *
From @projectList

Drop Table @projectList -- does not work

- jtpereyda

---

你不能自己删除它们，所以你问题的第二部分不适用。 - Martin Smith

Answer 1

变量是自动本地化并自动删除的 -- 您不必担心它。

- Hogan

---

+1 - 即使你想要删除它们，也不能这样做——只要会话保持打开状态，它们就会持续存在，就像任何其他变量一样。此外，它们不受事务的影响。 - JNK

@JNK提到的他们不受交易影响的观点非常重要，您可以使用表变量来保存数据，并在错误导致回滚后写入日志表。 - HLGEM

不，它们不一样。临时表参与事务。 - Paul Perigny

它们也不同于CTEs。 - Hogan

你不能丢弃它们，但你可以删除它们 'delete @projectList' ;) - R K Sharma

只是为了明确起见，DELETE @projectList 将从表变量 @projectList 中删除所有行。 - Hogan

Answer 2

如果其他人遇到类似情况...并且你确实需要在循环中删除它，你可以直接从表变量中删除所有内容：

DELETE FROM @tableVariableName

- Leo Much

---

当在循环中声明时，变量的id会递增，但不会重新设置为1。 - nitinsridar

这将删除表变量中的所有行，因此在循环的下一次迭代中它将为空。这符合我的要求。谢谢。 - Rahul Varadkar

Answer 3

表变量就像int或varchar变量一样。

您不需要丢弃它们。它们与int或varchar变量具有相同的作用域规则。

变量的作用域是可以引用该变量的Transact-SQL语句范围。变量的作用域从声明它的点开始，持续到声明它的批处理或存储过程的结束。

- gbn

---

你知不知道通过表变量创建的tempdb对象在超出范围时是否会被清理？还是服务器会等到会话关闭前才清理它们？ - StriplingWarrior

Answer 4

但是你们都忘了提到，如果在循环中使用变量表格，需要在再次加载数据之前清空它（delete @table）。

- Peppe

Answer 5

就像临时表一样，局部表变量也是在TempDB中创建的。表变量的作用域是它被声明的批处理、存储过程和语句块。它们可以作为参数在各个过程之间传递。当您关闭创建它们的会话时，它们会自动删除。

- Gagan

---

#temp表并不同于@table变量，它不会在批处理或作用域结束后自动删除，只有当它是在存储过程内创建的且存储过程执行完毕后才会自动删除。 - Abou-Emish

Answer 6

临时表变量保存在 temp.db 中，并且范围仅限于当前执行。因此，与删除临时表不同（例如 drop table #tempTable），我们不必显式删除临时表变量 @tempTableVariable。它会被 SQL Server 自动处理。

drop table @tempTableVariable -- Invalid

- Rubysmita Seth

Answer 7

实际上，您不需要删除@local_variable。

但是如果您使用#local_table，就可以做到这一点，例如，能够多次重新执行查询非常方便。

SELECT *
INTO #recent_records
FROM dbo.my_table t
WHERE t.CreatedOn > '2021-01-01'
;

SELECT *
FROM #recent_records
;

/*
  can DROP here, otherwise will fail with the following error
  on re-execution in the same window (I use SSMS DB client):

  Msg 2714, Level ..., State ..., Line ...
  There is already an object named '#recent_records' in the database.
*/
DROP TABLE #recent_records
;

你也可以将你的SELECT语句放在一个事务中，以便能够重新执行而无需显式地删除：

BEGIN TRANSACTION

  SELECT *
  INTO #recent_records
  FROM dbo.my_table t
  WHERE t.CreatedOn > '2021-01-01'
  ;

  SELECT *
  FROM #recent_records
  ;

ROLLBACK

- Dmitry Karpenko

Answer 8

这里是一个解决方案

Declare @tablename varchar(20)
DECLARE @SQL NVARCHAR(MAX)

SET @tablename = '_RJ_TEMPOV4'
SET @SQL = 'DROP TABLE dbo.' + QUOTENAME(@tablename) + '';

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tablename) AND type in (N'U'))
    EXEC sp_executesql @SQL;

在 SQL Server 2014 上运行良好 Christophe

- CSonneck