SQL Server 中的表变量

表变量是局部变量的特殊部分，它允许我们**临时保存完整的表记录。**它们类似于 SQL Server 中的临时表。它最初由 Microsoft 在 SQL Server 2000 中作为临时表的替代方案引入。通常，表变量支持局部变量的所有属性，但与临时表或标准表不同，局部变量有一些限制。

表变量的声明与局部变量类似。表变量的名称以**@ 符号**开头，其类型为表。其声明还可以定义带数据类型和约束的列名。我们还可以在其作用域内的 SELECT、INSERT、UPDATE 和 DELETE 语句中使用表变量。与永久表或常规表不同，我们不能在 SELECT 语句中使用 INTO 子句创建表变量。

语法

以下是 SQL Server 中声明表变量的语法

在语法中，我们将在**DECLARE**关键字之后首先编写**表变量名称**。我们必须确保表变量的名称以 @ 符号开头。接下来，我们将像标准表一样定义其结构，包括列名、数据类型、大小和约束。

表变量作用域

SQL Server 可以在批处理、函数和存储过程中声明表变量。表变量在批处理结束时超出作用域，类似于局部变量。当我们在函数或存储过程中声明表变量时，表变量也会在存储过程或函数退出后超出作用域。

表变量示例

让我们通过一些示例来了解如何在 SQL Server 中声明和使用表变量。下面的示例声明了一个名为**@months_table**的表变量，它包含三列：**number、month** 和**name**：

声明表变量后，我们将使用 INSERT 语句向其中插入数据

我们可以使用 SELECT 语句从表变量中查询数据，就像查询临时表一样。我们应该注意，我们将同时执行整个批处理。否则，SQL Server 将抛出错误消息**“必须声明表变量 @Months_Table。”**现在，执行以下语句

下图显示了部分输出

如果您想**更新**表变量中的任何**数据**，您必须使用以下语句

执行整个批处理，我们可以看到第二行将成功更新

表变量的存储位置

SQL Server 总是将表变量存储在**tempdb 数据库**中。我们知道表变量的**生命周期从声明点开始，到批处理结束时结束。**因此，它将在批处理结束时自动在 SQL Server 中删除。我们可以通过以下示例来理解这个概念

同时执行上面的批处理将显示两个结果集。我们可以分别在红色矩形和蓝色矩形中看到它们。

**红色矩形**输出显示了定义的表变量的列名、模式名和数据类型，而**蓝色矩形**输出不显示任何数据。这是因为第一个 INFORMATION_SCHEMA.COLUMNS 语句与表变量一起执行。因此，SQL Server 将显示 tempdb 数据库中表变量 @Months_Table 的信息。另一方面，第二个**INFORMATION_SCHEMA.COLUMNS**语句将不显示任何关于 @Months_Table 的数据。这背后的原因是 GO 语句结束了批处理，并且表变量的生命周期终止了。因此，它证明了 tempdb 数据库是 SQL Server 中表变量的存储位置。

表变量的限制

表变量在 SQL Server 中有一些限制。下面描述了一些限制

• 一旦表变量的结构在声明时定义，我们就不能像标准表或临时表那样更改表变量的结构。**例如：**

以上语句将显示以下错误

• 我们知道统计信息对于查询优化器构建有效的查询执行计划很有用。不幸的是，它不提供表变量。因此，表变量不能用于存储大量行。
• SQL Server 不允许表变量与**输入或输出参数**一起使用，这与其他数据类型不同。另一方面，用户定义函数可以返回表变量。
• 表变量不与**非聚集索引**一起使用，因此我们无法在表变量中创建它们。但是，**隐式索引**定义可以解决这个问题。这背后的原因是 SQL Server 可以通过使用 PRIMARY KEY 约束或 UNIQUE 约束定义自动创建索引，我们可以使用这些 INDEX 语句创建单个或复合非聚集索引。
• 表变量不允许我们使用 FOREIGN KEY 约束。
• 表变量不能与 TRUNCATE 语句一起使用来删除表中的所有数据。**例如：**

此语句将显示以下错误

• 如果我们将表变量与**JOIN**一起使用，我们需要为表名使用**别名**才能执行查询。**例如：**

事务和表变量

SQL Server 中的事务是用于管理**CRUD**（插入、选择、更新和删除）操作的单个逻辑单元。我们可以使用***BEGIN TRANSACTION***语句启动显式事务，并使用***COMMIT***或***ROLLBACK***语句完成它。让我们执行下面的查询，然后分析它们的结果

在此输出中，我们可以看到修改后的数据无法擦除表变量，因为在此处，CRUD 操作由显式事务管理。

存储过程中的表变量

存储过程是一组或多组预编译的 SQL 语句，组织成一个逻辑单元。SQL Server 中的每个过程总是包含一个名称、参数列表和 Transact-SQL 语句。以下示例将解释如何在存储过程中使用表变量。

以上语句创建了一个名为**“SP_Months_Table”**的存储过程，我们在其中也使用了表变量。我们可以使用**EXEC**语句调用此过程。请看下面的输出

用户定义函数中的表变量

用户定义函数是指由用户定义以执行特定任务的函数。用户将根据自己的需求创建此类函数。以下示例将解释如何在用户定义函数中使用表变量。

以上语句创建了一个名为**“UDF_TableVariable”**的用户定义函数，我们在其中也使用了表变量。我们可以使用以下语句调用此函数

我们将获得以下输出

表变量与临时表的一些主要区别

以下是表变量和临时表之间的主要区别

• 当我们在存储过程中使用表变量而不是临时表时，重新编译次数会减少。
• SQL Server 中的表变量将比临时表使用更少的资源。
• 表变量在锁定和日志记录开销方面比临时表使用更少的资源，因此我们可以在可能的情况下使用它们。
• 表变量不能执行截断操作，而临时表可以执行。
• 当表变量使用事务时，它无法回滚数据。另一方面，临时表可以通过事务操作回滚数据。
• 表变量存储在 tempdb 数据库中，而不是像临时表一样存储在内存中。