Problem solve Get help with specific problems with your technologies, process and projects.

Storing business objects as a SQL CLR user-defined type

Thinking of storing business objects as SQL CLR user-defined types? SQL Server 2005 expert Adam Machanic discusses why you shouldn't.

Should I store all of my business objects in SQL Server 2005 as instances of CLR user-defined types?

Implementing a SQL CLR user-defined type (UDT) is as easy as adding a few additional pieces to a .NET class or structure. These include an attribute (SqlUserDefinedTypeAttribute), an interface (INullable) and a few additional methods of (Null() and Parse()). As a result of this simplicity, a skilled developer can convert a business object into a SQLCLR UDT in less than five minutes.

SQL Server 2005 was not designed to be used as an object-oriented database management system. It is still a standard SQL database management system DBMS, and the UDT capabilities should be thought of as a type of system extensions rather than objects. Developers should carefully weigh their options when deciding whether to use an existing business object as a CLR UDT.

Every time a method or property is accessed on an instance of a type, the instance must be deserialized before the method can be accessed. Because of this, it's best to rely on types that can be compared based on their serialized bytes. Developers should try to only use UDTs that can be used atomically to answer questions. For instance, the following C# class would probably not be a good candidate for a UDT:

class Product
   public string Name;
   public string Description;
   public decimal price;

If a query were written against a column of this type, each row would have to be de-serialized in order to answer a question such as, "What products cost $10.00?" This is because we can't assume that all $10.00 products have the same binary representation. Deserializing every row of a large table (i.e. a table with millions of products) could become a serious performance challenge.

Aside from the performance challenge there are normalization concerns. Given this type, for instance, how would a company store two descriptions for the same product, but ensure that the product only had a single valid price?

It is best to stick to types that can be used to answer questions without incurring the overhead of deserialization.

Do you have comments on this Ask the Expert Q&A? Let us know.

Dig Deeper on Microsoft SQL Server 2005

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.