SQL Server中的表变量

In this article, we will explore the table variable in SQL Server with various examples and we will also discuss some useful tips about the table variables.

在本文中，我们将通过各种示例探索SQL Server中的表变量，并且还将讨论有关表变量的一些有用技巧。

定义 (Definition)

The table variable is a special type of the local variable that helps to store data temporarily, similar to the temp table in SQL Server. In fact, the table variable provides all the properties of the local variable, but the local variables have some limitations, unlike temp or regular tables.

表变量是本地变量的一种特殊类型，它有助于临时存储数据，类似于SQL Server中的临时表。 实际上，表变量提供了局部变量的所有属性，但是局部变量有一些限制，这与临时表或常规表不同。

句法 (Syntax)

The following syntax describes how to declare a table variable:

以下语法描述了如何声明表变量：

DECLARE @LOCAL_TABLEVARIABLE TABLE
(column_1 DATATYPE, 
 column_2 DATATYPE, 
 column_N DATATYPE
)

If we want to declare a table variable, we have to start the DECLARE statement which is similar to local variables. The name of the local variable must start with at(@) sign. The TABLE keyword specifies that this variable is a table variable. After the TABLE keyword, we have to define column names and datatypes of the table variable in SQL Server.

如果要声明表变量，则必须启动类似于局部变量的DECLARE语句。 局部变量的名称必须以at（@）符号开头。 TABLE关键字指定此变量是表变量。 在TABLE关键字之后，我们必须在SQL Server中定义表变量的列名和数据类型。

In the following example, we will declare a table variable and insert the days of the week and their abbreviations to the table variable:

在下面的示例中，我们将声明一个表变量，并将星期几及其缩写插入到该表变量中：

DECLARE @ListOWeekDays TABLE(DyNumber INT,DayAbb VARCHAR(40) , WeekName VARCHAR(40))
 
INSERT INTO @ListOWeekDays
VALUES 
(1,'Mon','Monday')  ,
(2,'Tue','Tuesday') ,
(3,'Wed','Wednesday') ,
(4,'Thu','Thursday'),
(5,'Fri','Friday'),
(6,'Sat','Saturday'),
(7,'Sun','Sunday')	
SELECT * FROM @ListOWeekDays

At the same time, we can update and delete the data contained in the table variables. The following query delete and update rows:

同时，我们可以更新和删除表变量中包含的数据。 以下查询删除和更新行：

DECLARE @ListOWeekDays TABLE(DyNumber INT,DayAbb VARCHAR(40) , WeekName VARCHAR(40))
 
INSERT INTO @ListOWeekDays
VALUES 
(1,'Mon','Monday')  ,
(2,'Tue','Tuesday') ,
(3,'Wed','Wednesday') ,
(4,'Thu','Thursday'),
(5,'Fri','Friday'),
(6,'Sat','Saturday'),
(7,'Sun','Sunday')	
DELETE @ListOWeekDays WHERE DyNumber=1
UPDATE @ListOWeekDays SET WeekName='Saturday is holiday'  WHERE DyNumber=6
SELECT * FROM @ListOWeekDays

表变量的存储位置是什么？ (What is the storage location of the table variables?)

The answer to this question is – table variables are stored in the tempdb database. Why we underline this is because sometimes the answer to this question is that the table variable is stored in the memory, but this is totally wrong. Before proving the answer to this question, we should clarify one issue about the table variables. The lifecycle of the table variables starts in the declaration point and ends at the end of the batch. As a result, the table variable in SQL Server is automatically dropped at the end of the batch:

这个问题的答案是–表变量存储在tempdb数据库中。 我们之所以强调这一点，是因为有时这个问题的答案是表变量存储在内存中，但这是完全错误的。 在证明该问题的答案之前，我们应澄清一个有关表变量的问题。 表变量的生命周期始于声明点，结束于批处理末尾。 结果，SQL Server中的表变量将在批处理结束时自动删除：

DECLARE @ExperiementTable TABLE
( 
TestColumn_1 INT, TestColumn_2 VARCHAR(40), TestColumn_3 VARCHAR(40)
);
SELECT TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, DATA_TYPE
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'TestColumn%';
    
GO
SELECT TABLE_CATALOG, TABLE_SCHEMA, COLUMN_NAME, DATA_TYPE
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'TestColumn%';

As you can see, the previous query returns two result sets. The ResultSet-1 contains column names and data types of the declared table variable and the ResultSet-2 does not contain any data. The reason for this case is, the first INFORMATION_SCHEMA.COLUMNS view, and table variable executed in the same batch so we can get the information of the @ExperiementTable table variable from the tempdb database. The second query could not return any data about the @ExperiementTable because the GO statement ends the batch so the life-cycle of the @ExperiementTable table variable is terminated. In this section, we proved the storage location of the table variable in SQL Server.

如您所见，上一个查询返回两个结果集。 ResultSet-1包含已声明表变量的列名和数据类型，而ResultSet-2不包含任何数据。 这种情况的原因是，第一个INFORMATION_SCHEMA.COLUMNS视图和表变量在同一批中执行，因此我们可以从tempdb数据库中获取@ExperiementTable表变量的信息。 第二个查询无法返回有关@ExperiementTable的任何数据，因为GO语句结束了该批处理，因此@ExperiementTable表变量的生命周期终止了。 在本节中，我们证明了表变量在SQL Server中的存储位置。

我们如何在表变量中使用约束？ (How can we use constraints with the table variables?)

Constraints are database objects that ensure data integrity. Table variables allow us to create the following constraints:

约束是确保数据完整性的数据库对象。 表变量允许我们创建以下约束：

• Primary Key
  首要的关键
• Unique
  独特
• Null
  空值
• Check
  检查一下

In the following example, we will successfully use all types of constraints on the table variable seamlessly:

在以下示例中，我们将成功地无缝使用表变量上的所有约束类型：

DECLARE @TestTable TABLE
(ID INT PRIMARY KEY,
Col1 VARCHAR(40) UNIQUE,
Col2 VARCHAR(40) NOT NULL,
Col3 int CHECK (Col3>=18))
    
 INSERT INTO @TestTable
 VALUES(1,'Value1',12 , 20)
    
 SELECT * FROM @TestTable

On the other hand, Foreign Key constraints cannot use for the table variables. The other restriction is, we have to define the constraints when we are declaring the table variable otherwise, we experience an error. For example, the following query will return an error because of this restriction. We cannot alter the table structure after the declaration of the table variable:

另一方面，外键约束不能用于表变量。 另一个限制是，在声明表变量时必须定义约束，否则会遇到错误。 例如，由于此限制，以下查询将返回错误。 在声明表变量之后，我们无法更改表结构：

DECLARE @TestTable TABLE
(ID INT NOT NULL  )
    
ALTER TABLE @TestTable
ADD CONSTRAINT PK_ID PRIMARY KEY (ID)

SQL Server中的事务和表变量 (Transactions and table variable in SQL Server)

Transactions are the smallest logical unit that helps to manage the CRUD (insert, select, update and delete) operations in the SQL Server. Explicit transactions are started with BEGIN TRAN statement and they can be completed with COMMIT or ROLLBACK statements. Now we will execute the following query and then analyze the result:

事务是最小的逻辑单元，可帮助管理SQL Server中的CRUD （插入，选择，更新和删除）操作。 显式事务以BEGIN TRAN语句开始，并且可以用COMMIT或ROLLBACK语句完成。 现在，我们将执行以下查询，然后分析结果：

DECLARE @TestTable TABLE
(ID INT PRIMARY KEY,
Col1 VARCHAR(40) UNIQUE,
Col2 VARCHAR(40) NOT NULL,
 Col3 int CHECK (Col3>=18))
BEGIN TRAN
INSERT INTO @TestTable
 VALUES(1,'Value1',12 , 20)
    
 ROLLBACK TRAN
    
SELECT * FROM @TestTable

Table variable CRUD operations do not manage by explicit transactions. As a result, ROLLBACK TRAN cannot erase the modified data for the table variables.

表变量CRUD操作不由显式事务管理。 结果，ROLLBACK TRAN无法擦除表变量的修改数据。

表变量的一些有用技巧 (Some useful tips for the table variables)

TRUNCATE语句不适用于表变量 (TRUNCATE statement does not work for table variables)

The TRUNCATE statement helps to delete all rows in the tables very quickly. However, this statement cannot be used for table variables. For example, the following query will return an error:

TRUNCATE语句有助于快速删除表中的所有行。 但是，该语句不能用于表变量。 例如，以下查询将返回错误：

DECLARE @TestTable TABLE
(ID INT PRIMARY KEY,
Col1 VARCHAR(40) UNIQUE,
Col2 VARCHAR(40) NOT NULL,
 Col3 int CHECK (Col3>=18))
     
 INSERT INTO @TestTable
 VALUES(1,'Value1',12 , 20)
    
    
TRUNCATE TABLE @TestTable

表变量结构在声明后无法更改 (The table variable structure cannot be changed after it has been declared)

According to this tip interpretation, the following query has to return an error:

根据此技巧的解释，以下查询必须返回错误：

DECLARE @TestTable TABLE
(ID INT PRIMARY KEY,
Col1 VARCHAR(40) UNIQUE,
Col2 VARCHAR(40) NOT NULL)
        
ALTER TABLE @TestTable 
ADD Col4 INT

SQL Server中的表变量应在连接语句中使用别名 (The table variable in SQL Server should use an alias with the join statements)

If we want to join two or more table variables with each other or regular tables, we have to use an alias for the table names. The usage of this looks like this:

如果我们想将两个或多个表变量彼此连接或与常规表连接，则必须对表名使用别名。 其用法如下所示：

DECLARE @Department TABLE
(DepartmentID INT PRIMARY KEY,
DepName VARCHAR(40) UNIQUE)
        
INSERT INTO @Department VALUES(1,'Marketing')
INSERT INTO @Department VALUES(2,'Finance')
INSERT INTO @Department VALUES(3,'Operations ')
        
DECLARE @Employee TABLE
(EmployeeID INT PRIMARY KEY IDENTITY(1,1),
EmployeeName VARCHAR(40),
DepartmentID VARCHAR(40))
        
INSERT INTO @Employee VALUES('Jodie Holloway','1')
INSERT INTO @Employee VALUES('Victoria Lyons','2')
INSERT INTO @Employee VALUES('Callum Lee','3')
        
select * from @Department Dep inner join @Employee Emp
on Dep.DepartmentID = Emp.DepartmentID

表变量不允许创建显式索引 (The table variable does not allow to create an explicit index)

Indexes help to improve the performance of the queries but the CREATE INDEX statement cannot be used to create an index for the table variables. For example, the following query will return an error:

索引有助于提高查询的性能，但是CREATE INDEX语句不能用于为表变量创建索引。 例如，以下查询将返回错误：

DECLARE @TestTable TABLE
(ID INT PRIMARY KEY,
Col1 VARCHAR(40) UNIQUE,
Col2 VARCHAR(40) NOT NULL)
        
        
CREATE NONCLUSTERED INDEX test_index
ON @TestTable(Col1)

However, we can overcome this issue with the help of the implicit index definitions because the PRIMARY KEY constraint or UNIQUE constraints definitions automatically create an index and we can use these INDEX statements in order to create single or composite non-clustered indexes. When we execute the following query, we can figure out the created index which belongs to @TestTable:

但是，由于PRIMARY KEY约束或UNIQUE约束定义会自动创建索引，因此我们可以使用隐式索引定义来克服此问题，并且可以使用这些INDEX语句来创建单个或复合的非聚集索引。 当我们执行以下查询时，我们可以找出属于@TestTable的创建索引：

DECLARE @TestTable TABLE
(
    Col1 INT NOT NULL PRIMARY KEY ,
    Col2 INT NOT NULL INDEX Cluster_I1 (Col1,Col2),
    Col3 INT NOT NULL UNIQUE
)
        
        
SELECT 
ind.name,type_desc
FROM 
     tempdb.sys.indexes ind 
        
where ind.object_id=(
SELECT OBJECT_ID FROM tempdb.sys.objects obj WHERE obj.name  IN (
SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
WHERE  (COLUMN_NAME = 'Col1' OR COLUMN_NAME='Col2' OR COLUMN_NAME='Col3')
))

结论 (Conclusion)

In this article, we explored the table variable in SQL Server details with various examples. Also, we mentioned the features and limitations of the table variables.

在本文中，我们通过各种示例探索了SQL Server详细信息中的表变量。 此外，我们提到了表变量的功能和局限性。

翻译自: https://www.sqlshack.com/the-table-variable-in-sql-server/