[SQL Server] Notes for creating custom Aggregate functions

 

Added support for CLR in SQL Server 2005, which allows us to write functions, stored procedures, triggers, and other objects for SQL Server in C # and VB. NET languages. There is not much to say about how to create these objects. There are many searches on the Internet. Here we will talk about some noteworthy issues when creating Aggregate functions.

 

Custom Aggregate functions are implemented in the form of a value type object, which must be serialized into the database. If the object has fields of the Int or double type, there is almost no problem. However, if fields of the string type exist, an error message similar to the following may occur when creating an aggregate function in SQL Server;

 

Mark type sqlserverproject. Aggregate for local serialization, but the result of type aggregate field is string type (it is non-value type ). The native serialization type can only have field types that can be directly copied to the native structure. If you want to have any other types of fields, consider using other serialization formats, such as user-defined serialization.

 

The cause of this error is that for int, double, and other types of data, they directly correspond to the local data type used by the operating system, for example, int and double have corresponding types in C ++. In fact, their structures are the same in C # And C ++, therefore, these types of fields can be processed as native types during serialization. For fields of the class type, such as string, there is no corresponding data type in the operating system, so these fields cannot be serialized directly. You must manually add serializationCodeTo tell SQL Server how to serialize these types of fields.

 

The work we need to do is very simple, just implement the ibinaryserialize interface for the aggregate function object. For example:

 

Public   Struct Aggregate: ibinaryserialize {

Private StringResult;

# RegionIbinaryserialize Member

Public VoidRead (system. Io. binaryreader R ){

This. Result=R. readstring ();
}

Public VoidWrite (system. Io. binarywriter W ){

W. Write (This. Result );
}

# Endregion
}

The ibinaryserialze interface has two methods: the read () method restores the field from the serialized stream, that is, converting the binary data to a string. Here we do not need to manually convert it, because the binaryreader type parameters of this method have provided a series of conversion methods, the read prefix method can be used to convert different types of values from the binary stream. The write () method is used to write fields into the serialized stream, that is, to convert fields into binary data. Likewise, the binarywriter parameter has provided a series of conversion methods and can be directly called.

 

The problem seems to have been solved, but the following error 6222 may occur when you create an aggregate function again:

 

The Type sqlserverproject. Aggregate is marked for local serialization, but the result of the type aggregate field is invalid for local serialization.

 

This paragraph is confusing, and I have made great efforts to find a reason. But the final reason is still found. See the above Code for the declaration of the aggregate function object:

[Microsoft. sqlserver. server. sqluserdefinedaggregate (format. Native)]

This is automatically generated by Vs, but the problem is also caused by this Code. As long as we change the value after format to the following:

[Microsoft. sqlserver. server. sqluserdefinedaggregate (format. userdefined)]

The problem is solved. The cause is very similar to the previous problem, format. native means to use the local data type to save the value object that implements the aggregate function. It is valid only when all the fields in this object have the corresponding local data type, such as int, long. The problem occurs because I use a string field in this object.

 

Format. userdefined indicates that the fields in the object use the User-Defined type, that is, there is no corresponding local data type. String meets this condition, so you need to change native to userdefined.