通过创建用户自定义的聚合函数 (Aggregation Function)，我们可以扩增原 SQL Server 所提供的 Min、Max、Count、Sum、Avg 等聚合函数的功能。以往要针对分组的数据完成自定义聚合的操作，必须要取得数据集合，不管是在服务器端通过存储过程创建游标(Cursor)结构，还是在用户端应用程序通过游标或数组，利用循环运算完成迭代(iteration)与累加的工作。现在可以直接通过.NET 的程序代码完成聚合的逻辑，而为群组中每一条记录迭代调用的工作就交由查询执行引擎完成，并通过调用你所编写的函数取得聚合执行结果并返回。
而创建用户自定义的“聚合”函数需完成以下的步骤：
1. 声明与编写 public类(class)。类名称不得超过128个字符，且必须符合 SQL Server 对象的命名规则 。
2. 加上 Serializable 属性(Attribute)，并通过 SqlUserDefinedAggregate 属性说明该类或结构是用作用户自定义聚合函数。
3. 实现 Init、Accumulate、Merge 和 Terminate 等函数。
Serializable 属性告知 CLR 让该类实例的数据可以序列化放到临时数据表中。SqlUserDefinedAggregate 属性则与上一节所介绍的 SqlUserDefinedType 属性近似，通过 Format 参数设置序列化实例的方式。而该参数的各项举例设置如 UserDefined、Native 和 SerializedDataWithMetadata 等的定义可以参照上一节。SqlUserDefinedAggregate 属性另外提供以下的参数，其默认值都为 False，除了照这些设置而需要改变该类实际操作的方式外，SQL Server 的查询最优化(Query Optimizer)程序也会因为这些属性设置的不同而影响其最优化的判断：
l IsInvariantToDuplicates：当数据重复时是否聚合的结果不变，例如 Min 和 Max 等聚合函数就不会因为数据重复而改变结果，但Sum则会受影响。
l IsInvariantToNulls：聚合的结果是否会因为 Nulls 的出现而受影响，例如 Min 和 Max 等聚合函数就不会因为数据重复而改变结果，但 COUNT * 则会受影响。
l IsInvariantToOrder：聚合的结果是否会因为数据排序与否而受影响，如果不受顺序影响，则查询最佳化引擎在创建执行计划(execution plan)时有更大的弹性。
l IsNullIfEmpty：如果参与聚合的组是空的(empty)，也就是没有数据做运算时，是否要返回 NULL。
我们通过 Visual Studio 2005 打开创建 Aggregate 类的模板，他会自动放入上述必须完成的步骤中，即第三项描述的四个空的函数定义。在此就利用该模板做一个累加字符串的用户自定义聚合函数，程序代码范例如列表11-26：
程序代码列表11-26 通过 Visual Basic.NET 编写 SQL Server 2005 可用的聚合函数
<Serializable(), SqlUserDefinedAggregate(Format.UserDefined,MaxByteSize:=512)> _
Public Class CommaDelimit
Implements IBinarySerialize '自定义 Persist 聚合时的中间过程
'将数据集合转成逗号分隔的字符串
Private sb As StringBuilder, firstConcat As Boolean
Public Sub Init()
'初始化内部的数据结构
sb = New StringBuilder()
firstConcat = True
End Sub
Public Sub Accumulate(ByVal value As SqlString)
'SQL Server 会为每一条记录传入 SQL 的数据格式
‘第一条记录之前不需要有逗号，但之后的字符串需要以逗号分隔
If firstConcat Then
sb.Append(value)
firstConcat = False
Else
sb.Append("," & value.ToString())
End If
End Sub
Public Sub Merge(ByVal value As CommaDelimit)
'将 Aggregate 的第二个实例与第一个实例合并
'在运算的过程中可能产生中间的聚合运算结果
Accumulate(value.ToString())
End Sub
Public Function Terminate() As SqlString
'聚合运算结束后，返回运算的结果
Return sb.ToString()
End Function
'实现 IBinarySerialize 界面所需的方法，让我们所定义的用户自定义聚合
'的中间数据可以临时 persist
Public Sub Read(ByVal r As BinaryReader) Implements IBinarySerialize.Read
firstConcat = r.ReadBoolean()
sb = New StringBuilder(r.ReadString())
End Sub
Public Sub Write(ByVal w As BinaryWriter) Implements IBinarySerialize.Write
w.Write(firstConcat)
w.Write(sb.ToString())
End Sub
End Class
上述的程序代码编译无误后，测试用的 T-SQL 内容如下：
--定义 .NET CLR 所提供的用户自定义聚合函数
CREATE AGGREGATE CommaDelimit(@value nvarchar(200))
RETURNS nvarchar(2000)
EXTERNAL NAME YukonCLR.[YukonCLR.CommaDelimit]
接下来使用程序代码列表11-27的 T-SQL 语法创建数据表，并增加一些测试数据。
程序代码列表 11-27：测试用户自定义聚合函数
CREATE TABLE BookAuthors( BookID int NOT NULL, AuthorName nvarchar(200) ,CreatTime datetime )
INSERT BookAuthors VALUES(01, 'Aril' ,'2004/02/03')
INSERT BookAuthors VALUES(01, 'Taylor','2004/02/03')
INSERT BookAuthors VALUES(03, 'Steven','2004/02/03')
INSERT BookAuthors VALUES(02, 'Sandy' ,'2004/12/04')
INSERT BookAuthors VALUES(03, 'Lisa' ,'2004/02/03')
INSERT BookAuthors VALUES(02, 'Byron' ,'2004/12/04')
INSERT BookAuthors VALUES(04, 'Byron' ,'2003/05/04')
INSERT BookAuthors VALUES(04, 'Byron' ,'2003/03/04')
INSERT BookAuthors VALUES(002, null, null)
INSERT BookAuthors VALUES(002, null, null)
--使用聚合函数查询结果
SELECT BookID, dbo.CommaDelimit(AuthorName) AuthorName
FROM BookAuthors
GROUP BY BookID
上述程序代码列表 11-26 中，Query Processor 将调用 Init 函数来初始化代表聚合函数的类实例。Accumulate 方法处理聚合后字符串显示的方式，Query Processor 使用这个方法以累加计算聚合后的数据。例如，在程序代码列表 11-27中查找作者资料时，这个方法会先找到符合 Book ID=1 的有哪几位作者，Book ID=2 的有哪几位作者······然后再交由 Accumulate、Merge 等方法累加各 BookID 相等的数据，最后调用 Terminate 完成聚合的计算并返回结果。
在计算过程中有可能会将中继数据临时储存起来，也就是序列化(Serialize)我们所定义存放聚合结果的数据结构(此处是 StringBulder 的实例)到硬盘，并当需要时再转到存储器上，所以在程序代码列表 11-26 中实现了 IBinarySerialize 接口的 Read 和 Write 方法。
程序代码列表 11-27 的查询结果如图11-28，由于我们在以 T-SQL 的 Create Aggregate 语法定义自定义聚合函数时，设置了返回数据类型( RETURNS nvarchar(2000) )，因此 AuthorName 的字段类型便是nvarchar(2000)
图11-28 通过自定义的聚合函数进行字符串的相加
我们再通过Group By 不同的字段，对自定义聚合函数稍作测试。你可以比较下列 T-SQL 语法与上述有何不同，执行结果如图11-29：
图11-29 通过自定义的聚合函数做字符串的相加
SELECT BookID, dbo.CommaDelimit(AuthorName) BookAuthors,CreatTime 印刷日期
FROM BookAuthors
GROUP BY BookID,CreatTime
ORDER BY BookID
在此可以发现查询语法不同，所得到的聚合结果也不同。
由于图11-29的Query结果，得到的字段中含有 Null 且第四个范例数据有重复，因此我们再另定义一个聚合函数并命名为 CommaDelimit2，程序架构大致上与前述的 CommaDelimit 相同，只需在 Accumulate 方法中增加判断的程序代码，该函数修改如程序代码列表 11-28 所示：
程序代码列表11-28 修改 Accumulate 方法，对相同的数据以及 null 值不加入聚合结果
Public Sub Accumulate(ByVal value As SqlString)
'SQL Server 会为每一条记录传入 SQL 的数据格式
If firstConcat Then
sb.Append(value)
firstConcat = False
Else
'如果是 NULL 就不要 append
If Not value.IsNull Then
'若在已存在的字符串中找到同样的字符串，就不要 append
If sb.ToString.IndexOf(value.ToString()) < 0 Then
sb.Append("," & value.ToString())
End If
End If
End If
End Sub
'Merge 传进的对象应就是自身类型的实例
Public Sub Merge(ByVal value As CommaDelimit2)
'将 Aggregate 的第二个实例与第一个实例合并
'在运算的过程中可能产生中介的聚合运算结果
Accumulate(value.ToString())
End Sub
另外需要稍作修改的是 Merge 函数，因为第二个范例是整个复制前一个 CommaDelimit 类，但改名成 CommaDelimit2 类，而 Merge 传进的对象应是自身类型的实例，因此须将参数类型稍作修改。
通过下列查询语法，我们得到了一个不一样的数据聚合结果。
SELECT BookID, dbo.CommaDelimit2(AuthorName) AuthorName
FROM BookAuthors
GROUP BY BookID
上述语法执行结果如下图11-30 所示：
图11-30 修改过的自定义聚合函数查询的结果
通过对象资源管理器窗口展开数据库“可编程性”节点下的“函数”，该函数内的“聚合函数”会显示我们所创建与设置的聚合函数，如图11-31 所示：
图11-31 通过对象资源管理器查看与管理我们在数据库内定义的聚合函数
所以当你想了解目前这个数据库已存在多少个自定义聚合函数时，也可用下列语法查询系统视图表。
SELECT * FROM sys.objects WHERE type='AF'
上述语法执行结果如图11-32 所示：
图11-32 通过系统视图表 sys.objects 查询数据库中已经建立的用户自定义聚合函数
最后，若你想要移除已经建立的用户自定义聚合函数，可以通过以下的 T-SQL 语法：
DROP AGGREGATE CommaDelimit
DROP ASSEMBLY YukonCLR
通过 .NET 创建 SQL Server 2005 中的存储过程、用户自定义函数、触发器、用户自定义数据类型，以及用户自定义聚合函数的方式就介绍到此，我们最后再来做个总结，讨论一下通过 .NET 程序语言构造这些对象，并在 CLR 上执行它们的优缺点。
责任编辑：cyth
您现在的位置： 