To UDT or Not to UDT

Managed UDTs have several significant limitations:

• Instance of a UDT is limited to 8KB.

• Database objects that contain a UDT must be schema-bound; maintenance is therefore more complex and more expensive.

• You cannot change a UDT once you start using it. It is technically possible to do so (by exporting data, dropping database objects, and then using Drop and Create or Alter Assembly), but changes to the UDT would invalidate all tables and indexes that reference it. One side of this problem can be solved. If you are expecting methods to change, you could write properties in one class and methods in another class. Then, when you decide to change methods, you can rewrite them and redeploy them without affecting the data stored in the first class (database).

• It is possible to create overloaded methods in a managed UDT, but it is not possible to call them from Transact-SQL. However, it is possible to call them from inside the UDT.

• It is not possible to call a UDT from Transact-SQL constructors with parameters. Constructors without parameters are automatically executed when the UDT is implemented as a class (reference type) and not invoked at all if it is implemented as a struct (value type). However, within the type it is possible to invoke any type of constructor.

• All data in a single UDT must be read and written in a single update operation.

Tip 

Do not go overboard with types—the fact that you can implement something as a type does not mean that you should not try to implement it first as columns and tables.