C# 開發 SQL Server 2005 CLR 自訂彙總函式：字串串聯 (StrJoin) 實戰

需求場景：GROUP BY 字串串聯

SQL 內建彙總僅有 SUM/MAX/MIN/AVG/COUNT。需求：依 Age 分組，將 Name 以逗號串聯 → 20: "張三,王二"。SQL Server 2005 引入 CLR 整合，允許以 C# 撰寫使用者定義彙總 (UDA)。

完整實作程式碼

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

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined,           // 需自行實作序列化
    IsInvariantToDuplicates = false,
    IsInvariantToNulls = true,
    IsInvariantToOrder = false,
    IsNullIfEmpty = true,
    MaxByteSize = 8000            // 回傳值最大 8000 bytes
)]
public struct StrJoin : IBinarySerialize
{
    private StringBuilder _result;

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

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

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

    public SqlString Terminate()
    {
        return _result.Length > 0 ? new SqlString(_result.ToString()) : SqlString.Null;
    }

    // IBinarySerialize 實作：StringBuilder 需自行序列化
    public void Read(System.IO.BinaryReader r)
    {
        _result = new StringBuilder(r.ReadString());
    }

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

部署與啟用步驟

1. VS 2005 建立 SQL Server Database Project → 加入 Aggregate 項目
2. 編譯產生 .dll → 在 SSMS 執行 CREATE ASSEMBLY 部署至資料庫
3. 建立彙總函式：CREATE AGGREGATE dbo.StrJoin (@input NVARCHAR(MAX)) RETURNS NVARCHAR(8000) EXTERNAL NAME AssemblyName.[Namespace.StrJoin]
4. 必要設定：

   -- 1. 資料庫相容性等級至少 90 (SQL 2005)
   ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 90;
   
   -- 2. 啟用 CLR
   EXEC sp_configure 'clr enabled', 1;
   RECONFIGURE WITH OVERRIDE;
   

使用範例

SELECT Age, dbo.StrJoin(Name) AS Names
FROM SomeTable
GROUP BY Age;
-- 結果：
-- 18 | 錢六
-- 20 | 張三,王二
-- 21 | 李四
-- 22 | 趙五

關鍵限制與注意事項

• 序列化：非基本型別 (string, StringBuilder 等) 必須實作 IBinarySerialize，Format.UserDefined
• 大小限制：MaxByteSize 最大 8000 位元組，超過會截斷/報錯
• 效能：CLR 彙總比 T-SQL 游標/迴圈快，但大量資料仍建議在應用層處理
• 安全性：EXTERNAL_ACCESS/UNSAFE 組件需簽章/強名稱
• 版本：SQL Server 2017+ 內建 STRING_AGG，無需自製 CLR