SQL Server CLR 實戰指南（三）：標量函數、表值函數與使用者定義彙總（UDA）

三類 CLR 函數對比

1. CLR 標量函數實作

1.1 無參數：查詢資料庫回傳單值

[Microsoft.SqlServer.Server.SqlFunction(
    DataAccess = DataAccessKind.Read,  -- 需讀取資料
    IsDeterministic = true)]
public static SqlString UF_Scalar_SayHello()
{
    string returnValue = "null";
    using (SqlConnection conn = new SqlConnection("context connection=true"))
    {
        conn.Open();
        SqlCommand com = new SqlCommand("SELECT TOP 1 [au_lname] FROM [dbo].[authors]", conn);
        using (SqlDataReader dr = com.ExecuteReader(CommandBehavior.CloseConnection))
        {
            if (dr.Read())
                returnValue = dr.GetString(0);
        }
    }
    return returnValue;
}

1.2 有參數：簡單邏輯處理

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString UF_Scalar_SayHelloByPar(SqlString par)
{
    return par;  // 直接回傳輸入參數
}

2. CLR 表值函數 (TVF) 實作

需三步驟：定義回傳類別 → 實作 IEnumerable 方法 → 實作 FillRow 方法。

2.1 定義回傳資料類別

public class ReturnData
{
    public SqlString Name { get; set; }
    public SqlString PassWord { get; set; }
    public ReturnData(string name, string passWord)
    {
        this.Name = name;
        this.PassWord = passWord;
    }
}

2.2 實作 TVF 主方法

[Microsoft.SqlServer.Server.SqlFunction(
    DataAccess = DataAccessKind.Read,
    FillRowMethodName = "FillRow_ReturnData",  // 指定填充方法
    IsDeterministic = true)]
public static IEnumerable UF_Table_GetReturnData()
{
    List list = new List();
    list.Add(new ReturnData("a", "a"));
    list.Add(new ReturnData("b", "b"));
    list.Add(new ReturnData("c", "c"));
    return list;  // 回傳集合
}

2.3 實作 FillRow 填充方法

public static void FillRow_ReturnData(object returnDataObj,
    out SqlString name,
    out SqlString passWord)
{
    ReturnData item = returnDataObj as ReturnData;
    name = SqlString.Null;
    passWord = SqlString.Null;
    if (item != null)
    {
        name = item.Name;
        passWord = item.PassWord;
    }
}

3. CLR 使用者定義彙總 (UDA) 實作

字串串聯彙總範例：SELECT dbo.StringAgg(au_lname) FROM authors → 'Author1,Author2,Author3,'

3.1 完整 UDA 類別實作

[Serializable]
[StructLayout(LayoutKind.Sequential)]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Format.UserDefined,           -- 自訂序列化
    IsInvariantToDuplicates = false,
    IsInvariantToNulls = true,
    IsInvariantToOrder = false,
    MaxByteSize = 8000)]
public class StringAgg : IBinarySerialize
{
    private StringBuilder strBuffer;

    // 1. 初始化
    public void Init()
    {
        strBuffer = new StringBuilder();
    }

    // 2. 累積單一值
    public void Accumulate(SqlString str)
    {
        if (!str.IsNull)
            strBuffer.Append(str.Value + ",");
    }

    // 3. 合併另一個彙總實例（平行查詢合併）
    public void Merge(StringAgg group)
    {
        Accumulate(group.Terminate());
    }

    // 4. 終結回傳結果
    public SqlString Terminate()
    {
        return new SqlString(strBuffer.ToString());
    }

    // 5. 序列化：寫入二進位流
    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(strBuffer.ToString());
    }

    // 6. 反序列化：從二進位流讀取
    public void Read(System.IO.BinaryReader r)
    {
        strBuffer = new StringBuilder(r.ReadString());
    }
}

4. T-SQL 部署腳本與呼叫範例

-- 假設組件已建立：CREATE ASSEMBLY CLRDemoAssembly FROM 'C:\CLRDemo.dll'

-- 標量函數
CREATE FUNCTION UF_Scalar_SayHello()
RETURNS NVARCHAR(32)
AS EXTERNAL NAME CLRDemoAssembly.UserDefinedFunctions.UF_Scalar_SayHello;
GO

CREATE FUNCTION UF_Scalar_SayHelloByPar(@Par NVARCHAR(32))
RETURNS NVARCHAR(32)
AS EXTERNAL NAME CLRDemoAssembly.UserDefinedFunctions.UF_Scalar_SayHelloByPar;
GO

-- 表值函數
CREATE FUNCTION UF_Table_GetReturnData()
RETURNS TABLE (Name NVARCHAR(32), PassWord NVARCHAR(32))
AS EXTERNAL NAME CLRDemoAssembly.UserDefinedFunctions.UF_Table_GetReturnData;
GO

-- 使用者定義彙總
CREATE AGGREGATE StringAgg(@Par NVARCHAR(32))
RETURNS NVARCHAR(MAX)
EXTERNAL NAME CLRDemoAssembly.StringAgg;
GO

-- 測試呼叫
SELECT dbo.UF_Scalar_SayHello();
SELECT dbo.UF_Scalar_SayHelloByPar('Hello CLR');
SELECT * FROM dbo.UF_Table_GetReturnData();
SELECT dbo.StringAgg(au_lname) FROM dbo.authors;  -- 彙總所有作者姓氏

屬性完整參考表

SqlFunctionAttribute 關鍵屬性

SqlUserDefinedAggregateAttribute 關鍵屬性

效能與選擇建議

• 標量函數：簡單邏輯用 T-SQL 内建函數；複雜運算、需存取外部資源用 CLR
• 表值函數：替代回傳結果集的預存程序，可直接 JOIN、APPLY，優化器可內聯（SQL 2019+）
• UDA：唯一能自訂彙總邏輯的方式（T-SQL 無法建立彙總），字串串聯、中位數、加權平均等必須用 CLR UDA
• 所有 CLR 物件部署需 clr enabled 伺服器選項啟用：EXEC sp_configure 'clr enabled', 1; RECONFIGURE;