在SQLServer2008中应用表值参数(TVP)

　　当我们浏览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

　　为了演示的目标，上面的SELECt语句只是硬编码一些值来插进;你通常可以从你的源系统表里履行SELECT DISTINCT命令来获得你盼看对其履行代理键查找功效的源系统键的列表。

　　返回成果显示了每一个源键的代理键。