将标量分解为行
经常需要在应用程序中传送多值参数例如在定单处理系统中可能需要编写存储过程来将定单插入到 Orders 表中存储过程中的参数之一可能是定单中的行项目在这种情况下您会遇到 TSQL 限制它不支持表值参数或缺乏集合数据类型（如数组）解决这个问题的一种方法是将集合编码为一个标量值（如 nvarchar 或 xml）然后将其作为参数传递给存储过程在存储过程内可以使用表值函数来接受标量输入并将其转换成一组行然后将这些行插入到 LineItems 表中
虽然可以用 TSQL 编写表值函数但是用 CLR 实现它有两个好处
SystemText 命名空间中的字符串处理函数使得编写表值函数更加容易
CLR TVF 提供了更有效的流实现这避免了将结果加载到工作表中
下面的代码片段显示了如何实现一个表值函数它接受以;分隔的一组值作为输入字符串并且以一组行（字符串中的每个值一行）的形式返回该字符串请注意MySqlReader 类的构造函数实现了大部分工作它使用 SystemStringSplit 方法将输入字符串分解为数组
// TVF that cracks a ; separated list of strings into a result
// set of nvarchar()column called Value
public static ISqlReader GetStrings(SqlString str)
{
return (ISqlReader)new MySqlReader(str);
}
public class MySqlReader : ISqlReader
{
private string[]m_strlist;
private int m_iRow = ; // # rows read
//The core methods
//Initialize list
public MySqlReader(SqlString str)
{
//Split input string if not database NULL;
//else m_strlist remains NULL
if (!strIsNull)
{
m_strlist = strValueSplit(;);
}
}
// SECTION: Metadata related: Provide # names types of
// result columns
public int FieldCount { get { return ; } }
public SqlMetaData GetSqlMetaData(int FieldNo)
{
if (FieldNo==)
return new SqlMetaData(Value SqlDbTypeNVarChar );
else throw new NotImplementedException();
}
// SECTION: Row navigation Read is called until it returns
// false After each Read call Get<TypeName> for each
// column is called
public bool Read()
{
//Return empty result set if input is DB NULL
//and hence m_strlist is uninitialized
if (m_strlist==null) return false;
m_iRow++;
if (m_iRow == m_strlistLength)
return false;
return true;
}
//Column getters
//Implement Get<SqlTypeName> for each column produced by
//the TVF; in this case just one
public SqlChars GetSqlChars(int i)
{
if (i == )
return new SqlChars(m_strlist[m_iRow]);
else
throw new NotImplementedException();
}
//Methods not used by SqlServer omitted;
//Actual implementation should provide an empty
//implementation
} // public class MySqlReader
} // class StringFunctions;
假定 GetStrings 方法注册为具有相同名称的 TVF下面是存储过程的代码片段它使用此 TVF 从定单中提取表形式的行项目
CREATE PROCEDURE Insert_Order @cust_id int @lineitems
nvarchar()
AS
BEGIN
INSERT LineItems
SELECT * FROM dboGetStrings(@lineitems)
END
对数据进行自定义聚合
在许多情况下您可能需要对数据进行聚合这包括执行统计计算（如 avgstddev 等等）如果所需的聚合函数不是作为内置聚合函数直接支持的SQL Server 中有三种方法可以进行这样的自定义聚合
将聚合编写为用户定义的聚合 (UDA)
使用 CLR 存储过程编写聚合
使用服务器端光标
让我们在一个称为 PRODUCT(int) 的简单聚合函数的上下文中检查这三种替代方法该聚合函数计算一组给定值的乘积
作为用户定义的聚合函数实现的 PRODUCT
下面是此函数的主干 C# 代码示例所有的积累逻辑都在 Accumulate 函数中（为了简单起见其他函数显示为 {}）
[SqlUserDefinedAggregate(FormatNative)]
public struct Product
{
public void Accumulate(SqlInt Value)
{
m_value *= Value;
}
public void Init() {}
public void Merge(Product Group) {}
public SqlInt Terminate() {}
}
在定义类型创建程序集和注册到 SQL Server 之后就可以通过以下方式使用 TSQL中的聚合函数
SELECT dboProduct(intcol)
FROM tbl
GROUP BY col
作为使用 SqlDataReader 的托管存储过程实现的 PRODUCT
可以创建存储过程来执行查询和循环访问结果以执行计算这种循环访问是通过使用 SqlDataReader 类完成的
[SqlProcedure]
public static void Product(out SqlInt value)
{
SqlCommand cmd = SqlContextGetCommand();
cmdCommandText = select intcolumn from tbl;
SqlDataReader r = cmdExecuteReader();
bool first = true;
using (r)
{
while (rRead()) //skip to the next row
{
if (first)
{
value = rGetSqlInt();
first = false;
}
else
{
value *= rGetSqlInt();
}
}
}
}
可以使用 EXEC 语句来调用这一过程
EXEC Product @p OUTPUT
作为使用光标的 TSQL 存储过程实现的 PRODUCT
可以创建 TSQL 存储过程来执行查询和通过使用 TSQL 光标循环访问结果以执行计算
create procedure TSQL_ProductProc (@product int output)
as
begin
declare @sales int
declare c insensitive cursor for select intcolumn from tbl
open c
fetch next from c into @sales
if @@FETCH_STATUS =
set @product = @sales
while @@FETCH_STATUS =
begin
fetch next from c into @sales
set @product = @product * @sales
end
close c
deallocate c
end
决定是使用 UDA 还是使用其他某种解决方案来产生结果取决于几个因素
可组合性要求UDA 实际上是独立的对象可以用于任何 TSQL 查询通常用在可以使用系统聚合函数的任何相同的地方不需要假定它所操作的查询例如可以将其包括在视图定义（不过索引视图中不支持 UDA）和标量子查询中
聚合算法细节在 Order By 子句（如果查询中有）之前可能对 UDA 进行求值因此不能保证传递给聚合函数的值的顺序如果聚合算法需要按照特定的顺序使用值则不能使用 UDA同样地UDA 从整组中使用值并且返回单一值如果需要必须为组中的每个值返回值的聚合函数则应该考虑使用存储过程或流表值函数来编写您的函数详细信息请参见本文中的产生结果一节
对副作用和数据访问的需要不允许 UDA 进行数据访问或有副作用如果您的函数需要保留大量的数据作为聚合的中间状态或因为其他某种原因需要进行数据访问则必须使用过程
使用 UDA 的第一种方法在这三个选择中可能提供最好的性能通常如果没有碰到上面所列的限制就应该尝试将聚合函数编写为 UDA如果无法使用 UDA 方法则使用 SqlReader 的托管代码方法可能比 TSQL 光标方法执行得更好
可以用 UDA 方法编写的有用的聚合的示例还包括找到每组中第 N 大（或第 N 小）值找到每组中前 N 个最大值的平均值或总和等等
用户定义的类型 (UDT)
现在我们来讲 SQL Server 中功能更强大但是经常被错误理解的一个功能使用用户定义的类型 (UDT)可以扩展数据库的标量类型系统（不仅仅为系统类型定义您自己的别名这在 SQL Server 以前的版本中一直可用）定义 UDT 就像用托管代码编写类创建程序集然后使用create type语句在 SQL Server 中注册该类型一样简单下面是实现 UDT 的主干代码
[SqlUserDefinedTypeAttribute(FormatNative)]
public struct SimpleUdt: INullable
{
public override string ToString() {}
public bool IsNull { get; }
public static SimpleUdt Null { get; }
public static SimpleUdt Parse(SqlString s) {}
}
create type simpleudt from [myassembly][SimpleUdt]
create tab
