SQLCLR 開發系列（五）：自訂聚合函數實作指南

前言

在 SQL Server 中，內建聚合函數（如 MAX、COUNT）雖然方便，但無法滿足所有業務需求。自 SQL Server 2005 起，透過 SQLCLR 整合，開發人員可以使用 .NET 語言撰寫自訂聚合函數，極大擴展了資料庫的計算能力。本文將詳細介紹如何建立、部署與使用自訂聚合函數，並以合併同一本書的多位作者為例進行實戰演示。

核心概念：SqlUserDefinedAggregate 屬性

自訂聚合函數在 Visual Studio 2005 中以結構體形式呈現，需標註 [Serializable] 與 [SqlUserDefinedAggregate] 屬性。關鍵屬性設定如下：

• Format.UserDefined：使用 CLR 序列化中繼結果
• IsInvariantToNulls = true：忽略 NULL 值不影響結果
• IsInvariantToDuplicates = false：重複值會影響結果
• IsInvariantToOrder = false：資料順序會影響結果
• MaxByteSize = 8000：持久化值最大位元組數

完整程式碼實作：Concatenate 聚合函數

以下程式碼摘自 SQL Server 2005 聯機叢書，功能為將相同 BookID 的作者名稱以逗號合併：

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

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToNulls = true,
    IsInvariantToDuplicates = false,
    IsInvariantToOrder = false,
    MaxByteSize = 8000)]
public class Concatenate : IBinarySerialize
{
    private StringBuilder intermediateResult;

    public void Init()
    {
        this.intermediateResult = new StringBuilder();
    }

    public void Accumulate(SqlString value)
    {
        if (value.IsNull) return;
        this.intermediateResult.Append(value.Value).Append(',');
    }

    public void Merge(Concatenate other)
    {
        this.intermediateResult.Append(other.intermediateResult);
    }

    public SqlString Terminate()
    {
        string output = string.Empty;
        if (this.intermediateResult != null && this.intermediateResult.Length > 0)
        {
            output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
        }
        return new SqlString(output);
    }

    public void Read(BinaryReader r)
    {
        intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this.intermediateResult.ToString());
    }
}

關鍵方法解析

Init()

初始化內部資料結構，在聚合開始前調用一次。

Accumulate(SqlString value)

處理每一筆輸入資料，將非 NULL 值附加到 StringBuilder，並加上分隔符號。

Merge(Concatenate other)

平行處理時合併部分聚合結果，將另一個實例的中繼結果追加至當前實例。

Terminate()

聚合結束時調用，移除尾端逗號並回傳最終字串。

Read/Write (IBinarySerialize)

實作自訂二進位序列化，確保中繼結果在記憶體與磁碟間正確傳輸。

資料庫部署與測試

建立測試資料表

CREATE TABLE BookAuthors
(
    BookID int NOT NULL,
    AuthorName nvarchar(200) NOT NULL
);

INSERT BookAuthors VALUES(1, 'Johnson');
INSERT BookAuthors VALUES(2, 'Taylor');
INSERT BookAuthors VALUES(3, 'Steven');
INSERT BookAuthors VALUES(2, 'Mayler');
INSERT BookAuthors VALUES(3, 'Roberts');
INSERT BookAuthors VALUES(3, 'Michaels');

呼叫自訂聚合函數

SELECT BookID, dbo.MyAgg(AuthorName)
FROM BookAuthors
GROUP BY BookID;

執行結果

管理與維護

部署成功後，所有 SQLCLR 物件（含自訂聚合）均可在 SSMS 的「可程式性」資料夾下對應模組中找到，方便集中管理。

結語

自訂聚合函數讓開發人員能以熟悉的 .NET 語言實現複雜的資料彙總邏輯，突破 T-SQL 內建函數的限制。掌握其開發模式（Init/Accumulate/Merge/Terminate）與部署流程，將為資料庫應用帶來極大的靈活性。