Large user-defined types and aggregates in SQL Server 2008

One last SQLCLR feature I'd forgotton about but was quite highly publicized. This is extension of SQLCLR UDT and UDAgg maximum size from 8000 bytes to 2gb. You just the MaxByteSize of -1 in the appropriate attribute, like this:

[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize=-1,IsNullIfEmpty=true)]

The serialization format is going to have to be UserDefined, the limt for Format.Native is still 8000 bytes.

Just catalog the UDT or UDAgg and use it as you would any other TYPE/AGGREGATE. I crufted up a proof-of-concept one pretty easily, and the new spatial data types (GEOGRAPHY and GEOMETRY) are also large (system) UDTs, so you know this works as of CTP5.

Now you can have your "infinite" UDT that holds an array or "infinite" string concatenation UDAgg. Enjoy.

4 thoughts on “Large user-defined types and aggregates in SQL Server 2008

  1. 2gb… what size of RAM should be used to handle and proceed on this kind of data?
    How to write 2gb data to memmory in single proces without timeout?

  2. You do need to monitor excessive SQLCLR memory use; this feature is likely to be used for data that can be close to but can be just over 8000 bytes. Some of the current data types have a limit of 2gb and the new spatial types, exposed as CLR types can sometimes exceed 8000 bytes.


  3. hello
    but I have VS2008 pro and SQLSERVER2008 developer and have error with maxbytesize=-1 for 2gig:
    Error 1 ‘MaxByteSize’ property specified was not found. SqlServerProject8
    thanks for your attention

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.