用户定义表类型（User-Defined Table Types）是SQL Server中的一种数据类型，允许开发者定义表结构并将其作为变量在存储过程、函数或批处理中使用。这种类型的表可以用来存储临时数据集，并且可以作为参数传递给存储过程或函数，这在处理批量数据时非常有用。

特点

• 复用性：用户定义表类型可以在数据库中定义一次，然后在多个存储过程和函数中重复使用。
• 参数传递：表类型可以作为参数传递给存储过程或函数。
• 性能优势：对于大量数据操作，使用表类型参数通常比传统的单个值参数性能更好。
• 只读：在存储过程或函数中，表类型参数是只读的。

应用场景

批量数据处理

当需要将大量数据作为输入传递给存储过程或函数时，表类型非常有用。

复杂数据结构

在需要操作复杂数据结构时，可以使用表类型来定义这些结构，并在代码中传递和操作。

临时数据存储

表类型可以在存储过程或函数中用于存储临时数据集，以便进行进一步的处理。

示例

以下是使用用户定义表类型的一些示例，包括数据表结构和测试数据脚本。

示例1：创建用户定义表类型

-- 创建一个用户定义的表类型
CREATE TYPE dbo.EmployeeTableType AS TABLE (
    EmployeeID INT,
    EmployeeName NVARCHAR(50),
    Department NVARCHAR(50)
);

示例2：使用用户定义表类型作为存储过程参数

假设我们有一个员工表（Employees），我们想要更新特定员工的部门信息。

数据表结构与测试数据脚本

-- 创建Employees表
CREATE TABLE dbo.Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(50),
    Department NVARCHAR(50)
);

-- 插入测试数据到Employees表
INSERT INTO dbo.Employees (EmployeeID, EmployeeName, Department) VALUES
(1, 'John Doe', 'Sales'),
(2, 'Jane Smith', 'IT'),
(3, 'Emily Jones', 'Sales');

创建存储过程

-- 创建一个存储过程，接受一个用户定义的表类型作为参数
CREATE PROCEDURE dbo.UpdateEmployeeDepartments
    @EmployeeUpdates dbo.EmployeeTableType READONLY
AS
BEGIN
    UPDATE e
    SET e.Department = eu.Department
    FROM dbo.Employees e
    INNER JOIN @EmployeeUpdates eu ON e.EmployeeID = eu.EmployeeID;
END;

调用存储过程

-- 声明一个表变量并插入需要更新的数据
DECLARE @EmployeeUpdates dbo.EmployeeTableType;

INSERT INTO @EmployeeUpdates (EmployeeID, Department) VALUES
(1, 'Marketing'),
(2, 'Human Resources');

-- 调用存储过程，并传递表变量作为参数
EXEC dbo.UpdateEmployeeDepartments @EmployeeUpdates;

在这个示例中，我们首先创建了一个用户定义的表类型EmployeeTableType，然后创建了一个存储过程UpdateEmployeeDepartments，它接受一个用户定义的表类型作为参数。最后，我们声明了一个与该类型相匹配的表变量@EmployeeUpdates，插入了需要更新的数据，并调用了存储过程。

示例3：使用用户定义表类型进行批量插入

创建存储过程

-- 创建一个存储过程，使用用户定义的表类型进行批量插入
CREATE PROCEDURE dbo.InsertEmployees
    @NewEmployees dbo.EmployeeTableType READONLY
AS
BEGIN
    INSERT INTO dbo.Employees (EmployeeID, EmployeeName, Department)
    SELECT EmployeeID, EmployeeName, Department
    FROM @NewEmployees;
END;

调用存储过程

-- 声明一个表变量并插入新员工的数据
DECLARE @NewEmployees dbo.EmployeeTableType;

INSERT INTO @NewEmployees (EmployeeID, EmployeeName, Department) VALUES
(4, 'Michael Scott', 'Management'),
(5, 'Dwight Schrute', 'Sales');

-- 调用存储过程，并传递表变量作为参数
EXEC dbo.InsertEmployees @NewEmployees;

在这个示例中，我们创建了一个存储过程InsertEmployees，它使用用户定义的表类型EmployeeTableType作为参数来执行批量插入操作。我们声明了一个表变量@NewEmployees，插入了新员工的数据，并调用了存储过程。

用户定义表类型在SQL Server中是处理批量数据操作的强大工具。它们提供了一种灵活的方式来定义和传递复杂的数据结构，从而简化了数据库编程并提高了性能。在设计数据库应用程序时，应考虑何时使用用户定义表类型来优化数据处理流程。