【SQL】CLR聚合函數什么鬼之前寫過一個合并字符串的CLR聚合函數，基本是照抄MS的示例，外加了一些處理，已經投入使用很長時間，沒什么問題也就沒怎么研究，近日想改造一下，遇到一些問題，遂搗鼓一番，有些心得，記錄如下。
一、雜項- CLR聚合函數在SQL中雖然是函數，但在C#中實際上是以一個類或結構的形式存在的（是的，實現聚合函數的實體可以是結構，也可以是類），這點有別于CLR普通函數，后者是類中的一個方法，鑒于此，下文把實現聚合函數的類或結構統稱為聚合類，以免讀者思維混淆
- 聚合類必須用特性[SqlUserDefinedAggregate]進行描述。MS示例中還用了[Serializable]特性，經測這個特性不是必須的
- 聚合類在執行時總是會被序列化和反序列化，這就要求聚合類要滿足可序列化，詳情在下面有說
- SqlUserDefinedAggregate特性的IsInvariantToNulls、IsInvariantToDuplicates、IsInvariantToOrder、IsNullIfEmpty這幾個屬性是給SQL引擎的查詢處理器使用的，大概是用作優化執行計劃的依據之類，跟聚合類的實現沒有關系。什么意思，比如IsInvariantToNulls=true，是告訴SQL查詢處理器，我這個聚合函數不管你字段中有沒有null（指SQL中的null，下同），返回的結果都是一致的，那么查詢處理器可能就會根據這個來確定是否復用已存在的執行計劃；它并不是說，會自動幫你過濾掉傳入Accumulate方法的null值。換句話，設置IsInvariantToNulls=true后，Accumulate中該進來null的照樣會進來，你該處理的還得處理。同樣的，其它幾個屬性也是如此，它們不會幫你負責任何事，該你操的心還得操。所以既然如此，那么你的實現最好與上述屬性描述的一致，不然欺騙了查詢處理器，估計有好果子給你吃。就好比你跟MM說請她吃6塊錢的麻辣燙，實際上你只買了1塊錢的，后果自己腦補
二、執行順序經打斷點調試，聚合類是按如下順序執行：
- 進入Init方法。這是聚合開始后的第一步，試過給類加無參構造函數，但沒進去，這里聚合類就已經在暗自向你警告，不要把它當一般class看待。至于為什么，不知道，望高人解答
- 進入Accumulate方法（這一步不是必然發生的，稍后說明）。該方法的參數就是最后部署到SQL中后，調用聚合時可傳入的參數（SQL 2005只支持1個參數），相當于面向SQL的一個入口，要聚合的元素會逐個逐個被扔進來，進來一個執行一次，完了再進來一個再執行一次，所以該方法會根據要聚合的元素個數循環執行多次，當然，如果元素為0個，就不會執行該方法，會直接跳到下一步，這就是為什么說這一步不是必然發生的。需要說明的是，聚合函數的工作是以分組為一個周期，就是GROUP BY出來有幾組，聚合函數就會調用幾次，這里說的是每一次中的執行順序，所以Accumulate方法的循環次數是單組的行數，并不是所有組的行數。舉個栗子，GROUP BY出來2組，第1組有2行，第2組有3行，那么整個聚合函數會被調用2次，第一次中Accumulate會循環2次，完了進行下一步，整個周期完成后，再開始聚合下一個組，顯然，第二輪中Accumulate會循環3次
- 進行序列化。干毛要序列化，我也想知道，我只知道這步之后，聚合類的所有字段的值都會清空（準確說是重置為類型默認值），所以如果不在序列化時抓住機會趕緊保存數據的話，將會使之前在Init和Accumulate中做的工作全部泡湯，因為在下一步的反序列化過程中你將得不到任何數據，進而導致在最終的Terminate方法中將無數據可返回！所以序列化這一步一定要著重理解。這一步只有在自己實現序列化的情況下才看得出來發生過。簡單說聚合類的序列化行為分為兩種，由SqlUserDefinedAggregate特性的Format屬性指定，該屬性（是個枚舉類型）共有3個值：Unknown、Native、UserDefined，其中Unknown是作為一個缺省值存在，類似其它枚舉中的None之類，代表尚未設置，在使用SqlUserDefinedAggregate特性時，Format必須指定為Native或UserDefined，如果是Unknown，則會拋異常。所以聚合類的序列化行為就只能是Native、UserDefined兩種：
- Native。代表聚合類交給CLR去序列化和反序列化，不需要自己實現，看起來很美，但是Native方式有些前提，就是聚合類只能存在值類型的成員，不能有引用類型的成員，包括string，并且如果聚合類是class而不是struct，那必須用[StructLayout(LayoutKind.Sequential)]特性進行標記，如果不滿足上述條件，部署必敗。所以有時候你不能偷懶，必須用UserDefined方式
- UserDefined。意味著必須自己實現序列化和反序列化行為，具體是通過讓聚合類實現IBinarySerialize接口進行，正如MS示例那樣。該接口有兩個方法，public void Write(BinaryWriter w)和public void Read(BinaryReader r)，分別代表序列化和反序列化過程。終于說到重點了，在聚合類進行到序列化這一步時，你要負責把你想保存的所有數據都寫入到w（一個BinaryWriter實例）的基礎流中，具體可通過w的Write方法進行，也可以直接訪問w.BaseStream操作基礎流，或者像new BinaryFormatter().Serialize(w.BaseStream, obj)這樣，把整個對象用BinaryFormatter寫到基礎流中，總之方法多樣，屬于流的知識范疇，本文不贅述，反正最終目的就是把數據寫進w.BaseStream，保險起見，寫完以后可以w.Flush()一下。這里就要說到第二個重點了，事關可寫入的數據量的問題，SqlUserDefinedAggregate特性有個MaxByteSize屬性，當采用UserDefined方式時，必須指定該屬性，表示在序列化時最多可以寫進多少字節的數據。不指定就是0，就是什么數據也保存不了~玩毛。MaxByteSize可以設置的最大值是由SqlUserDefinedAggregateAttribute.MaxByteSizeValue常量決定的，而這個常量.net2.0-3.5都是8000，后續版本不知道有沒有變動。也就是說，序列化時，最多可以寫入8000字節的數據，可以保存4000漢字？哎喲不錯哦~NONONO，據我調試，w的編碼方式是UTF8（不確定跟環境有沒有關系，因為w是CLR負責傳入的，什么情況下傳入什么編碼的w，無從考究。如果是固定傳UTF8，那只能說有點坑非ASCII區的人民了），且不可更改，也就是1個漢字可能占據3~4個字節，按3字節也就2600個漢字左右，應當說很不富裕，只能求神拜佛應用中觸碰不到這個極限。所以我的意見，一定要省著用這個容量，只保存必要的數據，不要圖省事把整個整個的對象序列化進去。比如MS的示例就只把StringBuilder中的string塞進去，而沒有把整個StringBuilder對象塞進去
- 進行反序列化。上回說道，把想保存的數據序列化，到了這一步，自然就是把數據取出來。同樣，可以通過r（一個BinaryReader)的各種ReadXXX方法取，也可以訪問r.BaseStream操作基礎流取出數據。這里頭腦要保持清醒，就是取出數據以后是為了在Terminate方法中處理并返回結果，而不是非得把成員對象還原了，然后再去Terminate中操作對象。什么意思，還拿MS的示例說事，不過這次它是反面教材，在Read中得到之前保存的string以后，沒必要還原成StringBuilder，完全可以用一個string字段去接住，然后在Terminate處理該字段并返回就好了
- 進入Terminate方法。上面說過Accumulate是面向SQL的入口，而Terminate就是出口了，聚合計算的結果就是通過Terminate返回給調用者，所以該方法的返回類型就是在SQL中得到的類型。通過上文，知道在Accumulate和Terminate之間，隔了一個序列化與反序列化的環節，并且要知道在序列化后，類字段的值已經被清空過了，已經不是當初那個類字段了（除非在反序列化時你把它們還原了）。清楚這一點，你就應該知道像這樣的看起來天經地義的做法：
public void Accumulate(SqlString str){ s = str;}public SqlString Terminate(){ return new SqlString(s);}在聚合類中是極大的錯誤，除非s在序列化時得到保存并在反序列化時進行還原。
- 開始下一組的Init→Accumulate→序列化/反序列化→Terminate。當然如果沒有下一組，整個結束
完了？不是還有個Merge方法么，很抱歉，我也不知道這貨什么時候才會用到。在我多次調試中，始終沒遇到執行Merge的情況。根據MSDN文檔所述，我的猜測是，CLR并不保證在一次聚合中都使用同一個聚合類實例，它隨時有可能另開一個實例來工作，并利用新開實例的Merge方法將舊實例的數據并入新實例中，完了釋放舊實例。不知道這個猜測對不對，擼過高手若清楚，還望指教，謝過先。如果這個猜測沒錯的話，顯然Merge方法要做的就是把舊實例（other）的數據并入當前實例，具體應該怎么寫讀者應該已心中有數了。要注意的是，如果聚合類是設計為只處理非重復元素的話，那么可以保證在每個實例中存儲的元素都是唯一的，但兩個實例中的元素卻有可能存在相同，在實現Merge時要留意這一點，要確保并入后的數據仍然是唯一的。
三、最后目前在我看來，聚合類它雖然在C#中是個類/結構，但處處透著古怪，比如沒有執行構造函數，運行期間又要清空類字段并轉而采用序列化和反序列化的方式傳遞狀態，使它又不那么像一個正常的類，所以我建議在完全弄清楚它之前，不要使用一些OOP的手法去實現它，比如繼承重寫什么的，想都不要想，老老實實填空就好。另外，對于文中提出的疑惑，希望得到高手指教，再次謝過。
最后附上一枚改造好的字符串聚合（忽略null、空白、重復字串、移除首尾空白）：
using Microsoft.SqlServer.Server;using System;using System.Collections.Generic;using System.Data.SqlTypes;using System.IO;namespace AhDung.SqlClr{ [SqlUserDefinedAggregate( Format.UserDefined, IsInvariantToNulls = true, IsInvariantToDuplicates = true, IsInvariantToOrder = true, MaxByteSize = SqlUserDefinedAggregateAttribute.MaxByteSizeValue) ] public class JoinString : IBinarySerialize { string sptr, result; Dictionary<string, object> dic; public void Init() { sptr = string.Empty; dic = new Dictionary<string, object>(StringComparer.CurrentCultureIgnoreCase);//忽略大小寫 } public void Accumulate(SqlString str, SqlString separater) { if (sptr.Length == 0 && !separater.IsNull && !string.IsNullOrEmpty(separater.Value)) { sptr = separater.Value; } string s; if (str.IsNull || (s = str.Value.Trim()).Length == 0 || dic.ContainsKey(s)) { return; } dic.Add(s, null); } public void Merge(JoinString other) { foreach (string s in other.dic.Keys) { if (dic.ContainsKey(s)) { continue; } dic.Add(s, null); } } public SqlString Terminate() { return new SqlString(result); } public void Read(BinaryReader r) { result = r.ReadString(); } public void Write(BinaryWriter w) { string[] ss = new string[dic.Count]; dic.Keys.CopyTo(ss, 0); w.Write(string.Join(sptr, ss)); } }}- 完 -
