Large CLR User-Defined Types

APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

In SQL Server 2005, user-defined types (UDTs) in the common language runtime (CLR) were restricted to 8,000 bytes in size. This restriction has been lifted in SQL Server 2008 and later versions. CLR UDTs are now treated in a similar way to large object (LOB) types. That is, UDTs less than or equal to 8,000 bytes behave the same way as in SQL Server 2005, but larger UDTs are supported and report their size as "unlimited".

For more information, see Large CLR User-Defined Types (OLE DB) and Large CLR User-Defined Types (ODBC).

Use Cases

For ODBC, support for large UDTs includes the ability to send UDT values in pieces as data-at-execution parameters. This is done by using SQLPutData.

For OLE DB, support for large UDTs includes the ability to stream UDT values to and from the server by using ISequentialStream binding.

UDTs less than or equal to 8,000 bytes will behave as they did in SQL Server 2005. For OLE DB, you can still stream small UDTs by using ISequentialStream binding.

Sometimes native code will have to understand the contents of CLR UDTs, but will not have to instantiate managed objects. If this is the case, you can use custom serialization to convert UDT values on the server into a well known format for clients.

For applications that have existing data access code, you can exploit CLR UDT behavior on the client by retrieving UDTs through native APIs and instantiating them by using C++ CLI interop in mixed mode applications.

See Also

SQL Server Native Client Features