Whidbey ADO.NET 2.0 User Defined Types (UDTs) with Sql Server 2005

  • Article

You can now create your own objects in managed code and install them into Sql Server 2005 as data types!

I am going to spend the next week blogging about the new Sql Server 2005 User Defined Types (UDTs). This is a very powerful feature that allows you to define new data types for Sql Server in managed code, these data types can have methods that you can call directly on the database and you can use these objects on the client. The only problem I have with this feature is that I am not sure of how it is going to be used, please leave me feedback if you are currently working on integrating UDTs into a project.

So what are User Defined Types for Sql Server 2005?

  • User Defined Types behave just like any other data type on the server. You can create tables with UDT columns, use them as variables to stored procedures, pass them as parameters etc.

  • They are defined using any of the languages supported by .NET.

  • They expose properties and methods that can be invoked using TSQL on the server.

  • On the client you can use a UDT as you would any other object.

UDT Requirements: Unfortunately you can’t convert just any object you create into a UDT, there is actually a long list of requirements, I will go over these requirements with a magnifying glass over the next few blogs. The basic requirements are that you specify a SqlUserDefinedTypeAttribute (this will handle most of the magic), implement INullable and expose Parse and ToString methods.

UDT Restrictions: Again, a long list of restrictions to blog about. The biggest ones are that your UDT can’t go over 8k total of serialized data and that we don’t support inheritance.

UDT Serialization Formats: There are two serialization formats, Native and User Defined. Native is where we do the serialization for you, this requires that your user defined type only contains fields that are value types (this means that you can’t use fields of type string for example). User Defined gives you full control over the serialization of the object to you (this is just a fancy way of saying that you need to implement IBinarySerialize and write the serialization code for your object yourself).

Rambling out,

Standard Disclaimer: The information posted here is provided “AS IS” and confers no rights.