CLR UDT Caveats

CLR UDTs aren't intended to turn SQL Server into a full-blown object-oriented database. First of all, storing an entire object in one column can adversely affect query performance. In both SQL Server 2005 and 2008, any time a CLR UDT is queried, the entire type must first be read from disk. Imagine a situation in which you want to aggregate the salary value across a range of persisted Employee CLR UDTs. If the Employee CLR UDT has many other properties (e.g., name, title, start data, email address), a considerable amount of additional disk I/O, CPU, and memory will be needed to read/parse each CLR UDT. Likewise, a UDT must be persisted in its entirety when just one property is modified. Finally, storing an entire object in one column makes it much more difficult to enforce referential integrity through foreign keys, indexes, and constraints. Thus, as a rule of thumb, CLR UDTs should not be used as a replacement for tables but rather for smaller “structures” within a table.

Despite their potential in this regard, I rarely come across an application that uses CLR UDTs. Why is this? Here are several key obstacles and approaches for overcoming them:

  • 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.
Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.