用 C# 开发 SQL Server 2005 的自定义聚合函数

renheshi

在 SQL 中，经常需要对数据按组进行自定义的聚合操作，比如用逗号连接一系列表示 ID 的数字，但默认只有 SUM, MAX, MIN, AVG 等聚合函数。在 SQL Server 2005 中提供了编写 CLR 的托管代码的支持，我们可以用来写自定义的聚合函数。
比如对于如下数据：

Age	Name
20	张三
21	李四
20	王二
22	赵五
18	钱六

我们想得到

Age	Name
18	钱六
20	张三,王二
21	李四
22	赵五

需要实现一个聚合函数 StrJoin, 其功能是用逗号连接字符串。
预期的 SQL 语句如下：

select
    Age,
    dbo.StrJoin(Name) as Name
from
    SomeTable
要实现这个函数，用 Visual Studio 2005 建立一个 C# 的 Database 项目，项目模版选择 SQL Server 数据库。在项目管理器里添加一个 Aggregate 后，输入代码如下：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined,
    IsInvariantToDuplicates=false,
    IsInvariantToNulls=true,
    IsInvariantToOrder=false,
    IsNullIfEmpty=true,
    MaxByteSize=8000
)]
public struct StrJoin: IBinarySerialize {
    private StringBuilder _result;

    public void Init() {
        _result = new StringBuilder();
    }

    public void Accumulate(SqlString Value) {
        if (Value.IsNull) {
            return;
        } else {
            if (_result.Length > 0)
                _result.Append(",");
            _result.Append(Value.Value);
        }
    }

    public void Merge(StrJoin Group) {
        _result.Append(Group._result);
    }

    public SqlString Terminate() {
        if (_result.Length > 0) {
            return new SqlString(_result.ToString());
        }
        return new SqlString("");
    }

    #region IBinarySerialize Members

    public void Read(System.IO.BinaryReader r) {
        _result = new StringBuilder(r.ReadString());
    }

    public void Write(System.IO.BinaryWriter w) {
        w.Write(_result.ToString());
    }

    #endregion
}

这里不叙述详细的操作步骤，网上应该可以搜到很多。
其原理是该类中提供了几个模版方法：Init(), Accumulate(), Merge(), Terminate().
我们需要做的是在其中写自己的聚合逻辑即可。这几个方法的含义分别是初始化，扫描到一条记录时，合并，终止扫描。

需要注意以下几点：

1. 自定义聚集函数中，我们返回的数据会被序列化然后转换到 SQL Server 中，对一些数值类型 Framework 提供了默认的序列化机制，但其他一些 CLR 的类型比如 string 就必须自己实现序列化机制，也就是实现 IBinarySerialize 接口。

2. 返回值和 SQL Server 里定义的变量一样，受到 8000 字节的长度限制。

3. SQL Server 2005 必须设置兼容性级别为 "SQL Server 2005(90)", 否则会出现如下错误：

'EXTERNAL' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，
以启用此功能。有关存储过程 sp_dbcmptlevel 的信息，请参见帮助。
4. 需要开启 SQL Server 2005 对 CLR 的支持（如果没有打开的话）。
执行如下命令：

EXEC sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
GO