使用 SQL Server 2005中的 CLR 集成(10)

http://database.51cto.com 2005-11-14 17:18 Microsoft 我要评论(0)

摘要：本文描述了数据库应用程序开发人员和架构师如何利用 SQL Server 2005 中的 CLR 集成功能。本文对基于 CLR 的编程方式与 SQL Server 中支持的现有编程模型（如 TransacT-SQL 和扩展存储过程）进行了比较，并且强调了各自相对的优缺点。还提供了一组选择合适的编程替代方法的高级指导，以及一些示例和代码示例。
标签：SQL SQL Server SQL Server 2005

对数据进行自定义聚合

在许多情况下，您可能需要对数据进行聚合。这包括执行统计计算（如 avg、stddev 等等）。如果所需的聚合函数不是作为内置聚合函数直接支持的，SQL Server 2005 中有三种方法可以进行这样的自定义聚合：

•	将聚合编写为用户定义的聚合 (UDA)。
•	使用 CLR 存储过程编写聚合。
•	使用服务器端光标。

让我们在一个称为 PRODUCT(int) 的简单聚合函数的上下文中检查这三种替代方法，该聚合函数计算一组给定值的乘积。

作为用户定义的聚合函数实现的 PRODUCT

下面是此函数的主干 C# 代码示例。所有的积累逻辑都在 Accumulate 函数中（为了简单起见，其他函数显示为 {...}）。

[SqlUserDefinedAggregate(Format.Native)]
public struct Product
{   
   public void Accumulate(SqlInt32 Value)
   {
         m_value *= Value;
   }
   public void Init() {...}
   public void Merge(Product Group) {...}
   public SqlInt32 Terminate() {...}
}

在定义类型、创建程序集和注册到 SQL Server 之后，就可以通过以下方式使用 T-SQL中的聚合函数：

SELECT dbo.Product(intcol) 
FROM tbl
GROUP BY col

作为使用 SqlDataReader 的托管存储过程实现的 PRODUCT

可以创建存储过程来执行查询和循环访问结果，以执行计算。这种循环访问是通过使用 SqlDataReader 类完成的。

[SqlProcedure]
   public static void Product(out SqlInt32 value)
   {
      SqlCommand cmd = SqlContext.GetCommand();
      cmd.CommandText = "select intcolumn from tbl";
      SqlDataReader r = cmd.ExecuteReader();
      bool first = true;
      using (r) 
      {
         while (r.Read()) //skip to the next row
         {
            if (first)
            {
               value = r.GetSqlInt32(0);
               first = false;
            }
            else
            {
               value *= r.GetSqlInt32(0);
            }
         }
      }
   }

可以使用 EXEC 语句来调用这一过程

EXEC Product @p OUTPUT

作为使用光标的 T-SQL 存储过程实现的 PRODUCT

可以创建 T-SQL 存储过程来执行查询和通过使用 T-SQL 光标循环访问结果，以执行计算。

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 = 0  
   set @product = @sales  
   
   while @@FETCH_STATUS = 0
   begin
   fetch next from c into @sales
   set @product = @product * @sales
   end
   
   close c
   deallocate c
end

决定是使用 UDA 还是使用其他某种解决方案来产生结果取决于几个因素：

•	可组合性要求。UDA 实际上是独立的对象，可以用于任何 T-SQL 查询，通常用在可以使用系统聚合函数的任何相同的地方。不需要假定它所操作的查询。例如，可以将其包括在视图定义（不过，索引视图中不支持 UDA）和标量子查询中。
•	聚合算法细节。在 Order By 子句（如果查询中有）之前可能对 UDA 进行求值，因此不能保证传递给聚合函数的值的顺序。如果聚合算法需要按照特定的顺序使用值，则不能使用 UDA。同样地，UDA 从整组中使用值并且返回单一值。如果需要必须为组中的每个值返回值的聚合函数，则应该考虑使用存储过程或流表值函数来编写您的函数。详细信息请参见本文中的“产生结果”一节。
•	对副作用和数据访问的需要。不允许 UDA 进行数据访问或有副作用。如果您的函数需要保留大量的数据作为聚合的中间状态，或因为其他某种原因需要进行数据访问，则必须使用过程。

使用 UDA 的第一种方法在这三个选择中可能提供最好的性能。通常，如果没有碰到上面所列的限制，就应该尝试将聚合函数编写为 UDA。如果无法使用 UDA 方法，则使用 SqlReader 的托管代码方法可能比 T-SQL 光标方法执行得更好。

可以用 UDA 方法编写的有用的聚合的示例还包括：找到每组中第 N 大（或第 N 小）值，找到每组中前 N 个最大值的平均值或总和，等等。