SQL Server中的表值参数

Table-Valued Parameters aka TVPs are commonly used to pass a table as a parameter into stored procedures or functions. They are helpful in a way, we can use a table as an input to these routines and we can get rid of dealing more complex steps to achieve this process.

表值参数（又称TVP）通常用于将表作为参数传递到存储过程或函数中。 它们在某种程度上很有帮助，我们可以将表格用作这些例程的输入，并且可以摆脱处理更复杂的步骤来实现此过程的过程。

介绍 (Introduction)

Most of us have heard this question from the software developers who are familiar with SQL Server:

我们大多数人已经从熟悉SQL Server的软件开发人员那里听到了这个问题：

“How can we pass a list of parameters to a stored procedure or a function?”

“我们如何将参数列表传递给存储过程或函数？”

Basically, in this article, we will try to find the answer to this question. However, the short answer to this question can be like this:

基本上，在本文中，我们将尝试找到该问题的答案。 但是，对这个问题的简短回答可以是这样的：

“When a data-driven application needs a list of parameters to any routines, we can overcome this issue by using the TVPs”. In the next sections of this article, we will learn using table-valued parameters with straightforward examples. This way, we will build a better understanding to answer this question too.

“当数据驱动的应用程序需要任何例程的参数列表时，我们可以通过使用TVP来解决此问题”。 在本文的下一部分中，我们将通过简单的示例来学习如何使用表值参数。 这样，我们也将建立更好的理解来回答这个问题。

用户定义的表类型 (User-defined table types)

User-defined table types are the predefined tables that the schema definition is created by the users and helps to store temporary data. User-defined table types support primary keys, unique constraints and default values, etc. However, the noticeable capability of the user-defined table type is that it is used as the referenced table type when we want to pass a TVP to stored procedures or user-defined functions.

用户定义的表类型是架构定义由用户创建的预定义表，有助于存储临时数据。 用户定义的表类型支持主键，唯一约束和默认值等。但是，用户定义的表类型的显着功能是，当我们要将TVP传递给存储过程时，它被用作引用的表类型。用户定义的功能。

User-defined table types definition syntax looks like as below:

用户定义的表类型定义语法如下所示：

CREATE TYPE LessonType AS TABLE
(LessonId   INT, 
 LessonName VARCHAR(100)
)

When we execute the above query, we can see the created user-defined table type under the User-Defined Table Types folder in SQL Server Management Studio (SSMS).

执行上述查询时，我们可以在SQL Server Management Studio（SSMS）的“ 用户定义的表类型”文件夹下看到创建的用户定义的表类型。

在存储过程中使用表值参数 (Using Table-Valued Parameters in Stored Procedures)

TVPs reference their types from the user-defined table so they inherit the schema of the table. In this way, we can obtain a parameter that can transfer the multiple columns and rows into the stored procedure as input. In this manner, the multiple parameter values can pass as an input to the stored and then they can be handled by the stored procedure.

TVP从用户定义的表中引用其类型，因此它们会继承表的架构。 这样，我们可以获得一个参数，该参数可以将多个列和行作为输入传递到存储过程中。 以这种方式，多个参数值可以作为输入传递到存储的，然后可以由存储的过程来处理。

In this first example, we will insert multiple rows into a table with the help of the TVP. Firstly, we will create a table whose name is Lesson.

在第一个示例中，我们将在TVP的帮助下将多行插入到表中。 首先，我们将创建一个名为Lesson的表。

CREATE TABLE Lesson ( 
        Id    INT PRIMARY KEY, 
        LName VARCHAR(50)
                )

In the second step, we will create a stored procedure that can accept LessonType as a parameter. So, the stored procedure will gain the ability to take a table as a parameter. In this stored procedure, we will insert all rows of the @ParLessonType variable to the Lesson table.

在第二步中，我们将创建一个存储过程，该存储过程可以接受LessonType作为参数。 因此，存储过程将具有将表作为参数的能力。 在此存储过程中，我们将@ParLessonType变量的所有行插入到Lesson表中。

CREATE PROCEDURE Usp_InsertLesson 
@ParLessonType LessonType READONLY
AS
INSERT INTO Lesson
SELECT * FROM @ParLessonType

Tip: The TVPs must be declared read-only therefore we have used the READONLY keyword in the definition of the parameter. The reason for this usage method is that we cannot make any manipulation (INSERT, UPDATE, DELETE) on the TVP in the routine body.

提示： TVP必须声明为只读，因此我们在参数的定义中使用了READONLY关键字。 使用这种方法的原因是我们无法在例程主体中的TVP上进行任何操作（INSERT，UPDATE，DELETE） 。

In this last step, we will declare a TVP that is referenced from the LessonType table type and will pass it to the stored procedure.

在最后一步中，我们将声明从LessonType表类型引用的TVP，并将其传递给存储过程。

DECLARE @VarLessonType AS LessonType
 
INSERT INTO @VarLessonType
VALUES ( 1, 'Math'
       )
INSERT INTO @VarLessonType
VALUES ( 2, 'Science'
       )
INSERT INTO @VarLessonType
VALUES ( 3, 'Geometry'
       )
    
    
EXECUTE Usp_InsertLesson @VarLessonType

Finally, when we check the Lesson table, we can see that the multiple rows have been inserted into the table.

最后，当我们检查“ 课程”表时，可以看到该表中已经插入了多行。

SELECT * FROM Lesson

使用内存优化的表值参数 (Using Memory-Optimized Table-Valued Parameters)

In-memory OLTP, objects can provide an advantage to improve the performance of the queries. In this context, memory-optimized tables can enable us with more effective data access. Moving from this idea, it seems possible to create memory-optimized TVPs. The main advantage of using memory-optimized TVPs is minimizing tempdb activity.

在内存中的OLTP中，对象可以提供提高查询性能的优势。 在这种情况下，内存优化表可以使我们更有效地进行数据访问。 从这个想法出发，似乎有可能创建内存优化的TVP。 使用内存优化的TVP的主要优点是将tempdb活动最小化。

In the following example, we will create a memory-optimized table type:

在下面的示例中，我们将创建一个内存优化的表类型：

CREATE TYPE LessonType_MemOptimized AS TABLE
(LessonId  INT PRIMARY KEY NONCLUSTERED  HASH WITH (BUCKET_COUNT = 1000)
 , LessonName VARCHAR(100)
) WITH ( MEMORY_OPTIMIZED = ON )

In this syntax, the MEMORY_OPTIMIZED =ON clause defines that this table type is memory-optimized. In addition to this, we have created a hash index because the memory-optimized table requires at least one index in order to manage the data.

使用此语法， MEMORY_OPTIMIZED = ON子句定义此表类型是内存优化的。 除此之外，我们还创建了一个哈希索引，因为内存优化表至少需要一个索引才能管理数据。

Now, we will create a stored procedure that uses a memory-optimized table-type as an input.

现在，我们将创建一个存储过程，该过程使用内存优化的表类型作为输入。

CREATE PROCEDURE Usp_InsertLessonMemOpt
@ParLessonType LessonType_MemOptimized READONLY
AS
INSERT INTO Lesson
SELECT * FROM @ParLessonType

Let’s try to execute the Usp_InsertLessonMemOpt procedure with memory-optimized table-valued parameter.

让我们尝试使用内存优化的表值参数执行Usp_InsertLessonMemOpt过程。

DECLARE @VarLessonType_MemOptimized AS LessonType_MemOptimized
 
INSERT INTO @VarLessonType_MemOptimized
VALUES ( 4, 'Math_MemOptimized '
       )
INSERT INTO @VarLessonType_MemOptimized
VALUES ( 5, 'Science_MemOptimized '
       )
INSERT INTO @VarLessonType_MemOptimized
VALUES ( 6, 'Geometry_MemOptimized '
       )
    
EXEC Usp_InsertLessonMemOpt @VarLessonType_MemOptimized 
    
SELECT * FROM Lesson

监视表值参数的tempdb活动 ( Monitoring tempdb activity of the Table-Valued Parameters)

We mentioned that memory-optimized TVPs do not show activity on the tempdb database. In this part, we will prove this concept. At first, we will launch the performance monitor and clear all existing counters with the delete key.

我们提到内存优化的TVP不会在tempdb数据库上显示活动。 在这一部分中，我们将证明这一概念。 首先，我们将启动性能监视器，并使用Delete键清除所有现有计数器。

In the second step, we will click the (+) sign in order to add a new counter. We will find out the SQL Server: Databases and then will add the Write Transaction/sec counter to the Added counters panel for the tempdb database.

在第二步中，我们将单击（ + ）符号以添加新的计数器。 我们将找到SQL Server：数据库 ，然后将Write Transaction / sec计数器添加到tempdb数据库的“ 添加的计数器”面板中。

At first, we will execute the following query in a loop 100000 times which will use the disk-based TVP:

首先，我们将循环执行100000次以下查询，该查询将使用基于磁盘的TVP：

TRUNCATE TABLE Lesson
GO
    
DECLARE @Counter AS INT=1
WHILE @Counter <= 100000
BEGIN 
DECLARE @VarLessonType AS LessonType
    
SET @Counter = @Counter+1 
    
INSERT INTO @VarLessonType
VALUES ( @Counter, 'Math'
           )
SET @Counter = @Counter+1
INSERT INTO @VarLessonType
VALUES ( @Counter, 'Science'
       )
SET @Counter = @Counter+1
INSERT INTO @VarLessonType
VALUES ( @Counter, 'Geometry'
       )
EXECUTE Usp_InsertLesson @VarLessonType
DELETE @VarLessonType
END

After completion of this query, we will execute the following query which will use the memory-optimized TVP.

完成此查询后，我们将执行以下查询，该查询将使用内存优化的TVP。

TRUNCATE TABLE Lesson
GO
    
DECLARE @Counter AS INT=1
WHILE @Counter <= 100000
BEGIN 
DECLARE @VarLessonType_MemOptimized AS LessonType_MemOptimized
    
SET @Counter = @Counter+1 
    
INSERT INTO @VarLessonType_MemOptimized
VALUES ( @Counter, 'Math'
       )
SET @Counter = @Counter+1
 
INSERT INTO @VarLessonType_MemOptimized
VALUES ( @Counter, 'Science'
       )
SET @Counter = @Counter+1
 
INSERT INTO @VarLessonType_MemOptimized
VALUES ( @Counter, 'Geometry'
       )
EXECUTE Usp_InsertLessonMemOpt @VarLessonType_MemOptimized
DELETE @VarLessonType_MemOptimized
END

When we analyze the performance monitor graph, it represents that the only disk-based TVP creates activity on tempdb.

当我们分析性能监视器图时，它表示唯一的基于磁盘的TVP在tempdb上创建活动。

In a word, memory-optimized TVPs usage reduces the tempdb activity despite of that this usage type may increase memory consumption. On the other hand, disk-based TVP creates activity on the tempdb.

总之，内存优化的TVP使用减少了tempdb活动，尽管这种使用类型可能会增加内存消耗。 另一方面，基于磁盘的TVP在tempdb上创建活动。

You can refer to “How to pass multiple parameters into an Inline table-valued function” part of this article, SQL Server inline table-valued functions to learn TVPs usage details with functions.

您可以参考本文的“ 如何将多个参数传递到内联表值函数中 ”， SQL Server内联表值函数，以了解该函数的 TVP使用情况。

结论 (Conclusion)

In this article, we learned about the Table-Valued Parameter usage details and we also mentioned memory-optimized TVPs performance issues. At this point, we will have to decide which TVP type provides maximum benefit according to the resource consumption and performance balance.

在本文中，我们了解了表值参数的用法详细信息，还提到了内存优化的TVP性能问题。 此时，我们将必须根据资源消耗和性能平衡来决定哪种TVP能够提供最大的收益。

翻译自: https://www.sqlshack.com/table-valued-parameters-in-sql-server/