在SQL Server 2008中运用表值参数(TVP)

网络整理 - 07-27

问题：
　　当我们浏览SQL Server 2008的新特性的时候，我们发现一个很有趣的特性叫做表值参数(Table-Valued Parameter)。你能否给我们详细介绍一下我们可以如何利用这个新特性吗?

　　专家解答：

　　表值参数确实是SQL Server 2008的一个新特性。顾名思义，表值参数表示你可以把一个表类型作为参数传递到函数或存储过程里。更高级的功能方面，表值参数的功能可以允许你向被声明为T-SQL变量的表中导入数据，然后把该表作为一个参数传递到存储过程或函数中去。表值参数的优点在于你可以向存储过程或函数发送多行数据，而无需向以前那样必须声明多个参数或者使用XML参数类型来处理多行数据。据说，表值参数可以处理多达1000行数据。

　　我们在这里将会介绍表值参数，并举一些简单的编码例子来演示如何通过使用表值参数来完成以下任务：

　　创建可以作为表值参数传递到函数或存储过程的表类型

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

　　声明表类型，向该表导入数据，并把它传递到存储过程里

　　在数据仓库应用程序里的数据加载过程，我们一般会在维度处理过程中把源系统键对应到代理键;然后使用代理键来识别数据仓库中的维度行。这样对维度行进行的每一个改动都会存储在一行带有新代理键的新行中，我们就可以保存维度行的完整历史记录。当对维度行进行改动或添加新行时，我们只需要给源系统键添加一个新的代理键并在维度表里插入新的一行就可以了。在处理事实行(fact rows)时，我们查找代理键并将其存储在事实表中。查询通过代理键连接事实表和维度表。由于多个事实表通常会索引至同一个维度(例如Customer)，代理键查找功能可以给我们提供一个使用表值参数的很好的例子。我们可以在存储过程中执行一次代理键查找，然后在多个事实表的数据载入过程中调用这个代理键查找。

除了简单的查找源系统键的代理键之外，还有一种情况可利用表值参数，即事实表含有一个不存在于维度表的源系统键。在这种情况下，我们则想要在维度里创建一个推断成员(Inferred member)，也就是说，创建一个新的代理键并把它添加到维度里，在我们从源系统里获得真正的维度行之后再把它替换更新掉。

　　下面的演示编码只在SQL Server 2008的二月份社区测试试用版中进行过测试。

　　新建一个表类型

　　为了把表作为一个参数传递到存储过程或函数中，你首先要创建一个TABLE TYPE，如下所示：
以下为引用的内容：
　　CREATETYPESourceKeyListASTABLE(
　　SourceKeyNVARCHAR(50)
　　)
　　GO

　　T-SQL编码跟新建一个普通的表的编码非常相似。你可以查询当前数据库的sys.types来确定已创建的任何表类型：
以下为引用的内容：
　　SELECTname,system_type_id,user_type_id
　　FROMsys.types
　　WHEREis_table_type=1

　　新建一个带有表值参数的存储过程

　　我们要创建一个存储过程，该存储过程执行代理键查找命令，如果源键不存在则添加一个推理成员。首先我们需要新建一个维度表样例：
以下为引用的内容：
　　CREATETABLEdbo.dim_Customer(
　　sk_CustomerINTIDENTITYNOTNULL,
　　CustomerSourceKeyNVARCHAR(50)NOTNULL,
　　CustomerNameNVARCHAR(50)NOTNULL,
　　InferredMemberBITNOTNULL
　　)

　　代理键是整数类型，我们使用IDENTITY属性来自动分配插入行的序列号。当我们插入一行源键不存在的行时，InferredMember列设置为1。当我们在维度处理过程中从源系统中获得该行后，它将替换掉推理成员行，而InferredMember列的值会变成0。
现在我们来创建一个接受新建表类型作为参数并执行代理键查找和推理处理的存储过程：
以下为引用的内容：
　　CREATEPROCEDUREdbo.stp_GetCustomerSK
　　@source_key_listSourceKeyListREADONLY
　　AS
　　BEGIN
　　INSERTINTOdbo.dim_Customer(
　　CustomerSourceKey,CustomerName,InferredMember
　　)
　　SELECTSourceKey,N'INFERRED',1
　　FROM@source_key_listk
　　LEFTJOINdbo.dim_CustomercONc.CustomerSourceKey=k.SourceKey
　　WHEREsk_CustomerISNULL
　　SELECTsk_Customer,CustomerSourceKey
　　FROMdbo.dim_Customerc
　　JOIN@source_key_listkONk.SourceKey=c.CustomerSourceKey
　　END
　　GO

　　表值参数必须被声明为READONLY。你不能对表值参数执行任何DML(即插入、更新、删除)。你职能在SELECT语句里引用它。存储过程把表值参数与客户维度连接起来，查找出任何还不存在的源键，然后插入这些键。然后存储过程再把表值参数与客户维度连接来返回含有源键及其对应代理键的结果集。

　　你可以通过查询sys.parameters来查看任何被声明为READONLY的参数：

以下是引用片段：
以下为引用的内容：
　　SELECTobject_id,nameFROMsys.parameters
　　WHEREis_readonly=1
　　GO

　　声明表值参数，导入数据，并把它传递到存储过程里

　　你声明一个表类型的T-SQL变量，使用INSERT插入语句向该表导入数据：
以下为引用的内容：
　　DECLARE@source_key_listSourceKeyList
　　INSERTINTO@source_key_list
　　SELECT'CustomerID_001'UNIONALL
　　SELECT'CustomerID_002'UNIONALL
　　SELECT'CustomerID_003'
　　EXECdbo.stp_GetCustomerSK@source_key_list
　　GO