CLR UDT Caveats

Many DBAs are reluctant to enable any type of CLR integration in their databases. They usually aren’t developers and don’t want “unknowns” in their database that could affect data availability, security, or performance. To gain DBA confidence, you might want to start small. For example, use the CLR to implement simple scalar functions (e.g., string manipulation logic) that require no direct data access and have the potential to perform better than T-SQL code.

When working with native SQL types (e.g., the integer type), passing data back and forth between SQL Server and a client application is very easy. ADO.NET has built-in support for the native types, and even non-.NET client applications can work with most native data types. CLR UDTs can be used in client applications, but the integration is a bit more difficult to achieve (e.g., in some cases, you’ll need to load the .NET assembly which defines the CLR UDT on both the server and client machines). To address this challenge, I suggest looking for opportunities to implement CLR UDTs that can be completely “transparent” to the application (i.e., you wrap the usage of UDTs inside stored procedures/functions/views).

Although BOL covers CLR UDTs (and ships with sample implementations), I've found that the documentation falls short in terms of explaining advanced scenarios for UDT instantiation. For instance, because of a lack of good examples, it took me quite a bit of time to learn how to instantiate a CLR UDT from within my T-SQL code by using something other than a string value. I believe this minor yet critical part of understanding UDTs has been a stumbling block for developers. In this article, I provide several examples of advanced instantiation.

As I mention in the main article, SQL Server 2005 restricts CLR UDTs to 8KB in size, limiting them to simple/smaller data types. Fortunately, SQL Server 2008 removes the 8KB limitation; CLR UDTs can now be up to 2GB in size.