asked
What are IBinarySerialize Interface methods used for?
When you create a custom aggregate function you need to specified the [enumeration format][1]: > Format Enumeration is used by SqlUserDefinedTypeAttribute and > SqlUserDefinedAggregateAttribute to indicate the serialization format > of a user-defined type (UDT) or aggregate. and when `UserDefined` format is used, your class need to implement [IBinarySerialize Interface][2] and override its `read` and `write` methods. My question is what exactly these methods need to do? Looking at the [examples][3], I guess they should be able to read/write the aggregation result? For example, I am trying to create a SQL CLR function that concatenates distinct numbers. In the T-SQL I can have from 1 to 255 distinct numbers (TINYINT value). I need to create a string from them (using delimiter), but sorting the numbers as well. The function seems to work, but I am not exactly sure I have override the methods as expected: [Serializable] [ Microsoft.SqlServer.Server.SqlUserDefinedAggregate ( Microsoft.SqlServer.Server.Format.UserDefined, IsInvariantToNulls = true, IsInvariantToDuplicates = true, IsInvariantToOrder = false, MaxByteSize = 1024 ) ] public class ConcatenateAnswersPos : Microsoft.SqlServer.Server.IBinarySerialize { private List intermediateResult; public void Init() { intermediateResult = new List (); } public void Accumulate(SqlByte value) { intermediateResult.Add((byte)value); } public void Merge(ConcatenateAnswersPos other) { intermediateResult.AddRange(other.intermediateResult); } public SqlString Terminate() { if (intermediateResult != null) { intermediateResult.Sort(); return new SqlString(string.Join(";", intermediateResult)); } else { return new SqlString(""); } } public void Read(BinaryReader r) { if (r == null) throw new ArgumentNullException("r"); intermediateResult = new List (); string[] answers = r.ReadString().Split(';'); foreach (string answer in answers) { intermediateResult.Add(Convert.ToByte(answer)); } } public void Write(BinaryWriter w) { if (w == null) throw new ArgumentNullException("w"); intermediateResult.Sort(); w.Write(string.Join(";", intermediateResult)); } } [1]:
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.format(v=vs.110).aspx [2]:
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.ibinaryserialize%28v=vs.110%29.aspx [3]:
http://msdn.microsoft.com/en-us/library/ms131056.aspx
The only time you need to implement the IBinarySerialize interface if you have a complex user defined type which the default implementation cannot serialize/deserialize. The native serialization works perfectly with any value types, but not the referenced types. TinyInt in T-SQL is byte in C#, and because it is a struct, there is no reason to wheel out your own serialization. In other words, you can simply comment out the : Microsoft.SqlServer.Server.IBinarySerialize part implementing the interface from you type definition and then comment out the Read and Write methods. Additionally, you will need to comment out the MaxByteSize part and also change the Format to Native. If you had a complex type which included some types which are translated to referenced type in C# then yes, you would have no choice but to wheel out your own implementation of the interface. For example, if you had a type which included a varchar member then you would have to do that. Varchar in T-SQL is a string in C# and string is authored as a sealed reference type. The Read and Write methods allow to create instance of the type from its serialized as binary data and pack it back to the binary from the instance of the type respectively, much like the instance of the type returned by the web service method is serialized before it is sent over network to receiving end and deserialized back into the instance of the type at the receiving end (except that the data is serialized as xml string for web service calls rather than binary). In reality, the same effect can be achieved without a custom aggregate though admittedly the custom aggregate is more convenient and makes the code clean. For example, if you select 20 rows from the master.dbo.spt_values table and would like to return the data as a delimited list of the numbers then something like this will do: select stuff( ( select ';' + cast(number as varchar) from master.dbo.spt_values where type = 'P' and number between 1 and 20 order by number for xml path('') ), 1, 1, ''); The above script will return this: result -------------------------------------------------- 1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20 Hope this helps. Oleg
Excellent answer, Oleg. I was too focused on the CLR question and didn't even think to reply about a TSQL method to achieve the same goal. Your comment about varchar reminded me that I wanted to play with CLR again by making a little string concatenation aggregate function and test performance vs. other methods like FOR XML PATH. Any thoughts or experience with that?
@Tom Staab Thank you. I believe that the CLR function will beat the select something cross apply (select something else where some condition matching the outside is met for xml path) handily once the number of outside rows is big enough, but I never found the performance of such queries to suffer enough to justify rolling out the CLR aggregate. I am curious about it too, but never have enough time to actually get to it and test the performance.
@Oleg Thanks a lot for the answer. Could you tell what exactly should not be a reference type in this class in order to use native serialization - only the parameter that Accumulate method except, the return value? And, could you tell if my `READ` and `WRITE` methods makes sense? I am sorry for the lame questions but I was not able to found detailed documentation or examples.
@Oleg Actually, I can't convert it to native, because I am using reference types - for example `Type "ClassLibrary2.ConcatenateAnswersPos" is marked for native serialization, but field "intermediateResult" of type "ClassLibrary2.ConcatenateAnswersPos" is not valid for native serialization.`
@gotqn I am terribly sorry, I overlooked that one (search for blittable in BOL to get the list of allowed value types which will not require the formal implementation of the interface). The good news is that the code you have does work, but I would make a couple of changes to it. First, your ConcatenateAnswersPos type does not have to be authored as a referenced type, usually the value types are used for that, so **public class ConcatenateAnswersPos** may become **public struct ConcatenateAnswersPos**. Also, the field named **intermediateResult** may be List of SqlByte instead of the List of byte so there is no need to cast the value in the Accumulate method. Depending on your requirements, it might be nice to place the check for null before adding the value (that is what standard aggregates usually do, they disregard nulls). Finally, you can restate the **Read** method like this (so there is no explicit looping): public void Read(BinaryReader r) { List list = new List(r.ReadString().Split(';')); intermediateResult = list.ConvertAll(i => (SqlByte)Convert.ToByte(i)); } The above assumes that intermediateResult is declared as list of SqlByte.
15 People are following this question.
