Here is a little commentary that I dispensed at my recent Nova CodeCamp presentation with regards to user defined aggregates written using SQL CLR for the Microsoft SQL Server. SQL 2008 has improved the user defined aggregate feature, but it currently comes at a price. First I discuss the attribute setting to implement the feature, then I provide necessary information on how to deploy it!
For those developers building user defined aggregates (UDA) in SQL Server 2008, you now can build an aggregate that exceeds the 8000 byte limitation in SQL Server 2005.
To implement this feature, you set the MaxByteSize to -1. This will allow the UDA to grow to a size above 8000 bytes up to 2 GB. An example of the attribute setting is shown below.
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToNulls = true,
IsInvariantToDuplicates = false,
IsInvariantToOrder = false,
MaxByteSize = -1)
]
Great so far. Now here is where the problem starts. You cannot deploy an assembly with a UDA having this setting from within Visual Studio 2008. You have to deploy the assembly manually.
/*
* Manually add assembly to database
*/
CREATE ASSEMBLY UDA from 'D:\Development\UDA\bin\UDA.dll'
WITH PERMISSION_SET = SAFE
GO
If you attempt to deploy the UDA with the above attribute, you will get the following message:
Error: 'MaxByteSize' property specified was not found.
You can find information on MSDN about the MaxByteSize setting here.