sql 表变量 临时表_SQL表变量概述

sql 表变量 临时表

This article explores the SQL Table variables and their usage using different examples.

本文使用不同的示例探索SQL Table变量及其用法。

介绍 (Introduction)

We use many intermediate tables to perform data manipulation, computation, and data sorting from multiple sources. It is beneficial to use temporary tables instead of a regular table and drop them later. Usually, we define SQL temporary tables using the # symbol:

我们使用许多中间表来从多个源执行数据操作，计算和数据排序。 使用临时表而不是常规表并在以后删除它们是有益的。 通常，我们使用＃符号定义SQL临时表：

CREATE TABLE #temp(DBname VARCHAR(20));
INSERT INTO #temp
        SELECT name
        FROM sys.sysdatabases;
SELECT *
FROM #temp;
DROP TABLE #temp;

A SQL temporary table uses following process.

SQL临时表使用以下过程。

• Define
  定义
• Use (Insert, update, delete, select)
  使用（插入，更新，删除，选择）
• Drop
  下降

In this article, we will look at an alternative way of storing temporary data, i.e. SQL table variables. Let’s take an overview of it along with multiple examples.

在本文中，我们将探讨存储临时数据（即SQL表变量）的另一种方法。 让我们对其进行概述，并提供多个示例。

SQL表变量概述 (Overview of SQL table variables)

The table variable is a special data type that can be used to store temporary data similar to a temporary table. The syntax for the table variable looks similar to defining a new table using the CREATE TABLE statement:

table变量是一种特殊的数据类型，可用于存储类似于临时表的临时数据。 table变量的语法看起来类似于使用CREATE TABLE语句定义新表：

DECLARE @Products TABLE
(ProductID INT,
ProductName VARCHAR(30)
);

In the above query, we specified a table variable using the DECLARE and TABLE keyword. The table variable name must start with the @ symbol. We also define table columns, data types, constraint similar to a regular table.

在上面的查询中，我们使用DECLARE和TABLE关键字指定了一个表变量。 表变量名称必须以@符号开头。 我们还定义了表列，数据类型和类似于常规表的约束。

SQL表变量的范围 (Scope of SQL table variable)

The table variable scope is within the batch. We can define a table variable inside a stored procedure and function as well. In this case, the table variable scope is within the stored procedure and function. We cannot use it outside the scope of the batch, stored procedure or function.

表变量范围在批处理之内。 我们也可以在存储过程和函数中定义一个表变量。 在这种情况下，表变量范围在存储过程和函数内。 我们不能在批处理，存储过程或函数的范围之外使用它。

将数据插入表变量 (Insert data into a table variable)

We can insert data into a table variable similar to a regular table:

我们可以将数据插入类似于常规表的表变量中：

DECLARE @Products TABLE
(ProductID   INT, 
 ProductName VARCHAR(30)
);
INSERT INTO @Products
VALUES
(1, 
 'Carpet'
);

You can retrieve a record from a table variable using the Select statement:

您可以使用Select语句从表变量中检索记录：

We cannot drop the table variable using the DROP Table statement. If you try to drop it, you get incorrect syntax message:

我们不能使用DROP Table语句删除表变量。 如果尝试删除它，则会收到不正确的语法消息：

We do not require dropping the table variable. As mentioned earlier, the scope of the table variable is within the batch. The scope of it lasts at the end of the batch or procedure.

我们不需要删除表变量。 如前所述，表变量的范围在批处理之内。 它的范围在批次或过程结束时持续。

SQL表变量的存储位置 (The storage location of SQL table variable)

Let’s think of a few questions:

让我们考虑几个问题：

• What is the storage location of a table variable? 表变量的存储位置是什么？
• Is it created in the source database in which we execute the script? 它是在执行脚本的源数据库中创建的吗？

Most people are confused about the table variable location and think that it is stored in memory. Let’s check it out.

大多数人对表变量的位置感到困惑，并认为它存储在内存中。 让我们来看看。

We can use sys.tables for listing tables in the tempdb database. Let’s execute the following query and do the following:

我们可以使用sys.tables在tempdb数据库中列出表。 让我们执行以下查询并执行以下操作：

• Check the existing tables in tempdb
  检查tempdb中的现有表
• Declare a table variable
  声明一个表变量
• Check the tables in tempdb again
  再次检查tempdb中的表

SELECT *
FROM TEMPDB.sys.tables;
GO
DECLARE @Products TABLE
(ProductID   INT, 
 ProductName VARCHAR(30)
);
Go
SELECT *
FROM TEMPDB.sys.tables;
GO

In the output, we did not get any existing table before and after declaring a table variable:

在输出中，在声明表变量前后，我们没有得到任何现有表：

Does it mean that the table variable is not stored in the tempdb database? No, it is not valid. You can note that we use Go statement after each step to finish the batch before starting the subsequent batch. Table variable scope finishes at the end of the batch, and SQL Server automatically drops it.

这是否意味着表变量未存储在tempdb数据库中？ 不，这是无效的。 您可以注意到，我们在每个步骤之后都使用Go语句来完成批处理，然后再开始下一个批处理。 表变量作用域在批处理结束时完成，SQL Server自动删除它。

Let’s run the following query. In the modified query, we removed the Go statement after declaring a table variable and checking the existence of it in the tempdb:

让我们运行以下查询。 在修改后的查询中，我们在声明表变量并检查tempdb中是否存在后删除了Go语句：

SELECT *
FROM TEMPDB.sys.tables;
GO
DECLARE @Products TABLE
(ProductID   INT, 
 ProductName VARCHAR(30)
);
SELECT *
FROM TEMPDB.sys.tables;
GO

Now, in the output, it shows the table variable in the tempdb database:

现在，在输出中，它显示了tempdb数据库中的表变量：

表变量和显式事务 (Table variable and explicit transaction)

We cannot use the table variable in the explicit transaction, it does not return any error message, but it skips the transaction.

我们不能在显式事务中使用表变量，它不会返回任何错误消息，但是会跳过事务。

In the following query, we execute the query in four steps:

在以下查询中，我们分四个步骤执行查询：

1. Declare a table variable
   声明一个表变量
2. Starts a transaction using the BEGIN TRAN statement
   使用BEGIN TRAN语句启动事务
3. Insert record into the table variable
   将记录插入表变量
4. Rollback transaction using ROLLBACK TRAN
   使用ROLLBACK TRAN的回滚事务
5. Verify whether the record exists in the table variable or not. It should not exist because we performed rollback in step 4
   验证记录是否存在于表变量中。 它不存在，因为我们在步骤4中执行了回滚

DECLARE @TableVariableA TABLE(Name VARCHAR(30));
BEGIN TRAN;
INSERT INTO @TableVariableA
VALUES('SQL Server');
ROLLBACK TRAN;
SELECT *
FROM @TableVariableA;
GO

In the output, we can verify that ROLLBACK TRAN did not perform a rollback of data from the table variable:

在输出中，我们可以验证ROLLBACK TRAN没有执行来自表变量的数据回滚：

If we require explicit transactions, we can use the temporary tables. The explicit transaction works on temporary tables.

如果需要显式事务，则可以使用临时表。 显式事务适用于临时表。

用户定义函数（UDF）和表变量 (User-defined functions (UDF) and table variable)

We can define and use table variables in the user-defined functions as well. Execute the following query:

我们也可以在用户定义的函数中定义和使用表变量。 执行以下查询：

1. Define a user-defined function using CREATE FUNCTION Statement
   使用CREATE FUNCTION语句定义用户定义的函数
2. Define a table variable and define columns for it
   定义一个表变量并为其定义列
3. Define UDF activity in the BEGIN…END statement
   在BEGIN…END语句中定义UDF活动
4. Return the variable value
   返回变量值

CREATE FUNCTION TableVariableWithinAFunction()
RETURNS @TableVariable TABLE(Name VARCHAR(50))
AS
    BEGIN
        INSERT INTO @TableVariable
                SELECT FirstName
                FROM [DemoSQL].[dbo].[Employee];
        RETURN;
    END;
GO

Later, we can use a UDF function to retrieve the records:

以后，我们可以使用UDF函数检索记录：

Select * from TableVariableWithinAFunction()

It retrieves the records from UDF:

它从UDF检索记录：

This example showed that we could use table variables in a user-defined function as well. We cannot use temporary tables inside a user-defined function.

这个例子表明，我们也可以在用户定义的函数中使用表变量。 我们不能在用户定义的函数内使用临时表。

索引和表变量 (Indexes and table variable)

Table variables are a particular type of data types. We can use these table variables similar to the user table to hold temporary data.

表变量是数据类型的一种特殊类型。 我们可以使用类似于用户表的这些表变量来保存临时数据。

Q: Is it possible to add indexes on the table variables?

问：是否可以在表变量上添加索引？

A: No, we cannot define indexes on the table variables.

答：不，我们不能在表变量上定义索引。

Q: If we cannot define indexes on table variables, do we have any alternatives for it?

问：如果我们不能在表变量上定义索引，是否有其他选择？

A: Yes, indeed, we cannot define index in the table variables, but we can define primary and unique key constraints on the table variables:

答：是的，的确，我们不能在表变量中定义索引，但是可以在表变量上定义主键约束和唯一键约束：

DECLARE @Customer TABLE
(CustomerId   INT
 PRIMARY KEY NONCLUSTERED, 
 CustomerName NVARCHAR(30) UNIQUE CLUSTERED(CustomerName)
);

We cannot define an explicit clustered and non-clustered index on the table variable. Primary key and unique key constraints automatically create the internal indexes on it. You can use these constraints to unique define rows in an index as well.

我们不能在表变量上定义显式的聚集索引和非聚集索引。 主键和唯一键约束自动在其上创建内部索引。 您也可以使用这些约束来唯一定义索引中的行。

Can we modify a SQL table variable structure after declaration?

声明后，我们可以修改SQL表变量结构吗？

A: No, we cannot alter a table variable definition after the declaration. Suppose you define a table variable for holding the Varchar data type of length 50. Later, our requirement changes, and we want to modify it for length 100.

答：不，我们不能在声明后更改表变量的定义。 假设您定义一个表变量以保存长度为50的Varchar数据类型。后来，我们的要求发生了变化，我们想将其修改为长度100。

We cannot alter a table variable structure. We can define another table variable. We can also modify the definition of an existing table variable.

我们不能更改表变量的结构。 我们可以定义另一个表变量。 我们还可以修改现有表变量的定义。

结论 (Conclusion)

In this article, we explored the SQL table variables and their usage for storing temporary data. We also compared it with the temporary tables. Let’s have a quick summary of what we have learned:

在本文中，我们探讨了SQL表变量及其在存储临时数据中的用法。 我们还将其与临时表进行了比较。 让我们快速总结一下我们学到的东西：

	Temporary table	SQL table variable
Syntax	We use the following format for defining a temporary table: Define Use Drop CREATE TABLE #Employee                 (Id INT, Name VARCHAR(50)) We define a temporary table using the hash sign.	In Table variable, we use the following format: Define Use We cannot drop a table variable using an explicit drop statement.
Storage	It is stored in the tempdb system database.	The storage for the table variable is also in the tempdb database.
Transactions	We can use temporary tables in explicit transactions as well.	Table variables cannot be used in explicit transactions.
User-defined function	We cannot use it in the user-defined functions.	We can use table variables in the UDF.
Indexes	We can define explicit indexes on the temporary tables.	We cannot define explicit indexes on table variables. We can use primary and unique key constraints.
Scope	Scope of a temporary table is local and global as defined while creating it.	Scope of the table variable is within the batch. We cannot use it outside the batch.

	临时表	SQL表变量
句法	我们使用以下格式定义临时表： 定义 用 下降 我们使用哈希符号定义一个临时表。	在Table变量中，我们使用以下格式： 定义 用 我们不能使用显式drop语句删除表变量。
存储	它存储在tempdb系统数据库中。	表变量的存储也位于tempdb数据库中。
交易次数	我们也可以在显式事务中使用临时表。	表变量不能用于显式事务中。
用户自定义功能	我们不能在用户定义的函数中使用它。	我们可以在UDF中使用表变量。
指标	我们可以在临时表上定义显式索引。	我们无法在表变量上定义显式索引。 我们可以使用主键和唯一键约束。
范围	临时表的范围是创建表时定义的局部和全局范围。	表变量的范围在批处理之内。 我们不能在批生产之外使用它。

翻译自: https://www.sqlshack.com/an-overview-of-the-sql-table-variable/

sql 表变量 临时表