[SQL Server]STRING_AGG 在2017版以前的替代方案(SQLCLR)

2018-05-26

6986
0
T-SQL

SQL Server 2017推出STRING_AGG 字串集合的串接函式，但限制是SQL2017的版本。上個月重新加入一個大型專案中，專案的版本要求是SQL 2016，Code Review時發現同事改用For Xml Path再Distinct的替代，T-SQL閱讀起來多了點複雜，捲手來救援T-SQL程式的可讀性。

因為只是少量字串資料的串接，一開始打算的替代方案是SQLCLR。有趣的是，微軟Docs示範CLR User-Defined Aggregate的範例就是字串集合的串接，哈哈，正所謂踏破鐵鞋無覓處，得來全不費功夫就是這個概念了，程式一刀未剪馬上可以用，來筆記這個開心。

開發SQLCLR

打開地表最強IDE的Visual Studio，熱鍵Ctrl + Shift + N新增一個資料庫專案，在左手邊的技術分類中，選SQL Server

熱鍵Ctrl + Shift + A 新增項目，從項目清單中選 SQL CLR C#彙總

填入Docs上Example 1的範例程式碼:

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

[Serializable]  
[SqlUserDefinedAggregate(  
    Format.UserDefined, //use clr serialization to serialize the intermediate result  
    IsInvariantToNulls = true, //optimizer property  
    IsInvariantToDuplicates = false, //optimizer property  
    IsInvariantToOrder = false, //optimizer property  
    MaxByteSize = 8000) //maximum size in bytes of persisted value  
]  
public class Concatenate : IBinarySerialize  
{  
    /// <summary>  
    /// The variable that holds the intermediate result of the concatenation  
    /// </summary>  
    private StringBuilder intermediateResult;  

    /// <summary>  
    /// Initialize the internal data structures  
    /// </summary>  
    public void Init()  
    {  
        this.intermediateResult = new StringBuilder();  
    }  

    /// <summary>  
    /// Accumulate the next value, not if the value is null  
    /// </summary>  
    /// <param name="value"></param>  
    public void Accumulate(SqlString value)  
    {  
        if (value.IsNull)  
        {  
            return;  
        }  

        this.intermediateResult.Append(value.Value).Append(',');  
    }  

    /// <summary>  
    /// Merge the partially computed aggregate with this aggregate.  
    /// </summary>  
    /// <param name="other"></param>  
    public void Merge(Concatenate other)  
    {  
        this.intermediateResult.Append(other.intermediateResult);  
    }  

    /// <summary>  
    /// Called at the end of aggregation, to return the results of the aggregation.  
    /// </summary>  
    /// <returns></returns>  
    public SqlString Terminate()  
    {  
        string output = string.Empty;  
        //delete the trailing comma, if any  
        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());  
    }  
}

熱鍵F6建置專案，接著切到bin的目錄，我們會發現有3個檔案(.dacpac .dll .pdb)

佈署SQLCLR及測試

可以用.dacpac佈署，但我們簡單使用"從dll建立組件"

CREATE  ASSEMBLY MyAgg FROM 'C:\Users\stanley\Documents\Visual Studio 2017\Projects\Database1\Database1\bin\Debug\Database1.dll'
WITH PERMISSION_SET = SAFE; 
GO  

CREATE AGGREGATE MyAgg (@input nvarchar(200)) RETURNS nvarchar(max)  
EXTERNAL NAME MyAgg.Concatenate;

建立完畢後，在指定資料庫的可程式性下，可以發現多了我們建立的組件與彙總函式

建立測試資料表並新增6筆測試資料

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

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

測試一下字串運算式值的串接，只要用使用MyAgg函式

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

依照BookID分組，將字串集合串接

xml path作法

SELECT DISTINCT
	BookID
	,STUFF((SELECT
			',' + AuthorName
		FROM BookAuthors A
		WHERE  A.BookID = B.BookID
		FOR XML PATH (''))
	, 1, 1, '') AS AuthorNames
FROM BookAuthors B