SQL Server表值参数如何高效传递和更新数据？

什么是表值参数？

表值参数是一种用户定义的表类型 (User-Defined Table Type)，你可以把它想象成一个“只读的表变量”或一个“数据行的集合”。

它的核心作用是：将多行数据从客户端应用程序（如 C#、Java、Python）一次性传递到 SQL Server，而不是在循环中为每一行数据都执行一次数据库操作。

为什么使用表值参数？（优点）

与传统的单行参数传递方式相比,使用表值参数有以下几个显著优点：

如何使用表值参数？（分步指南）

使用表值参数通常需要三个步骤：

1. 在 SQL Server 中定义表类型：创建一个自定义的表结构。
2. 创建一个接受该表类型参数的存储过程或函数。
3. 在客户端应用程序中，填充数据并调用该存储过程。

定义表类型

你需要使用 CREATE TYPE 语句在数据库中创建一个用户定义的表类型，这个类型定义了你的参数将包含哪些列以及这些列的数据类型。

-- 示例：创建一个名为 dbo.OrderListTT 的表类型
-- 它包含订单ID、产品ID和数量三列
CREATE TYPE dbo.OrderListTT AS TABLE
(
    OrderID INT,
    ProductID INT,
    Quantity INT
);

执行后,这个 dbo.OrderListTT 类型就像一个表模板，可以在数据库中被重用。

创建使用表值参数的存储过程

创建一个存储过程,并在其参数列表中使用上面定义的表类型作为参数类型，在存储过程内部，你可以像查询普通表一样使用这个表值参数。

-- 示例：创建一个存储过程，用于批量插入订单
CREATE PROCEDURE dbo.usp_InsertOrders
    -- 声明一个表值参数，类型为我们刚刚创建的 OrderListTT
    @OrdersToInsert AS dbo.OrderListTT READONLY 
AS
BEGIN
    SET NOCOUNT ON;
    -- 像使用普通表一样使用 @OrdersToInsert 参数
    -- 注意：表值参数必须是 READONLY 的，不能对它进行 DML 操作（如 INSERT, UPDATE, DELETE）
    INSERT INTO dbo.Orders (OrderID, ProductID, Quantity, OrderDate)
    SELECT 
        OrderID, 
        ProductID, 
        Quantity, 
        GETDATE() -- 假设订单日期为当前时间
    FROM 
        @OrdersToInsert;
    -- 可以返回受影响的行数
    SELECT @@ROW_COUNT AS 'RowsAffected';
END

关键点：

• @OrdersToInsert AS dbo.OrderListTT：这里声明了参数的类型。
• READONLY：这是必须的，SQL Server 规定，表值参数在存储过程内部是只读的，你不能修改传入的数据，也不能对它执行 INSERT, UPDATE, DELETE 操作，你只能从中 SELECT 数据。

在客户端应用程序中使用

这是表值参数最常用的场景,下面以 C# 为例，展示如何调用上面的存储过程。

using System;
using System.Data;
using System.Data.SqlClient;
public class TableValuedParameterExample
{
    public static void Main()
    {
        // 1. 定义数据库连接字符串
        string connectionString = "Server=your_server;Database=your_database;Integrated Security=True;";
        // 2. 创建并填充 DataTable，其结构必须与 SQL Server 中定义的表类型完全匹配
        DataTable orderTable = new DataTable();
        orderTable.Columns.Add("OrderID", typeof(int));
        orderTable.Columns.Add("ProductID", typeof(int));
        orderTable.Columns.Add("Quantity", typeof(int));
        // 添加一些示例数据行
        orderTable.Rows.Add(101, 5001, 2);
        orderTable.Rows.Add(102, 5002, 1);
        orderTable.Rows.Add(103, 5001, 5);
        // 3. 使用 SqlConnection 和 SqlCommand
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            // 4. 创建 SqlCommand 对象，并指定存储过程
            SqlCommand command = new SqlCommand("dbo.usp_InsertOrders", connection);
            command.CommandType = CommandType.StoredProcedure;
            // 5. 创建 SqlParameter，并将其类型设置为结构化 (Structured)
            SqlParameter tvpParam = command.Parameters.AddWithValue("@OrdersToInsert", orderTable);
            // 关键：将参数的 SqlDbType 设置为 Structured
            tvpParam.SqlDbType = System.Data.SqlDbType.Structured;
            // 6. 可选：如果需要，可以指定用户定义类型的名称（架构.类型名）
            // 如果参数名和类型名相同，这一步通常可以省略，但明确指定更安全
            tvpParam.TypeName = "dbo.OrderListTT"; 
            // 7. 执行命令
            int rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine($"成功插入了 {rowsAffected} 行订单数据。");
        }
    }
}

客户端代码关键点：

• DataTable：在客户端，你需要准备一个 DataTable，它的列名和数据类型必须与 SQL Server 中定义的表类型 (dbo.OrderListTT) 完全一致（列名不区分大小写，但数据类型必须兼容）。
• SqlDbType.Structured：这是最重要的设置，它告诉 ADO.NET 这个参数不是简单的值，而是一个结构化的数据集（即表）。
• TypeName：指定这个 DataTable 应该映射到 SQL Server 中的哪个用户定义表类型，这确保了类型安全。

限制和注意事项

1. 只读性：如前所述，表值参数在存储过程内部是 READONLY 的，不能对它进行修改。
2. 不能用于 DML 目标：你不能直接对表值参数执行 INSERT, UPDATE, DELETE。DELETE FROM @OrdersToInsert WHERE Quantity < 1 是不允许的。
3. 不能用于 OUTPUT 参数：表值参数不能被标记为 OUTPUT。
4. 最大行数和大小限制：表值参数也有大小限制（受 max_server_memory 影响，默认最大为 2GB），如果数据量非常大，可能需要考虑分批处理。
5. 性能：对于非常小的数据集（例如只有几行），直接执行多个单条语句可能更快，因为避免了序列化 DataTable 的开销，但对于几十行或更多的数据，表值参数的性能优势会非常明显。

表值参数是 SQL Server 中处理批量数据传递的标准且高效的方式，是编写高性能、可伸缩数据库应用程序的重要工具。