Format=UserDefined UDTs in Sql Server 2005

Today I am talking about Format=UserDefined User Defined Types (UDTs). Format=Native UDTs are leaner, meaner and faster but only support blittable types as fields (no Strings!). UserDefined UDTs support any field type since they require the user to implement the UDT serialization themselves (in Native UDT the server does all the work). This sounds scary, but all it actually means is that you need to implement a Read and a Write method.

Let’s look at a complete example:

using System;

using System.Data.SqlTypes; //for INullable

using System.Data.Sql; //for SqlUserDefinedType

using System.IO; //for binaryreader

[Serializable]

[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 8000)]

public class Contact : INullable, IBinarySerialize

{

            //<Items that define the UDT>

            public string FirstName; //important field that we want to serialize

            public string PersonState; //temporary field, don’t need to serialize

            public string SayHello()

            {

                        return "Hello, my name is " + this.FirstName;

            }

            public Contact(){ FirstName = "";}

            public Contact(string firstname) { this.FirstName = firstname; }

    //</Items that define the UDT>

            //<Items required for Format=UserDefined UDTs>

            public void Write(BinaryWriter w)

            {

                        w.Write(FirstName);

            }

            public void Read(BinaryReader r)

            {

                        FirstName = r.ReadString();

            }

    //</Items required for Format=UserDefined UDTs>

            //<Items required so that the UDT works>

            public static Contact Null { get { return new Contact(); } }

            public bool IsNull { get { return ((FirstName == ""));} }

            public static Contact Parse(SqlString data) {return new Contact(data.ToString());}

            public override String ToString() { return this.FirstName; }

            //</Items required so that the UDT works>

}

[SqlUserDefinedType(Format.UserDefined,

Not really worth a comment, this is where we indicate that this UDT is UserDefined

IsByteOrdered = true

This is our first BIG decision, do we want our UDT to support comparison operators, group by, order by and partition by? When IsByteOrdered is set the server will use the disk representation of the instance in binary comparisons. Operators supported include =,!-,<,<=,> and >=.

MaxByteSize = 8000

For UserDefined UDTs we need to specify what the max size of the _serialized_ UDT is going to be (not only the data). Sql Server supports up to 8k inline so you can’t have UDTs that are greater than that. You can specify a smaller MaxByteSize if you know that your UDT is not going to grow beyond a certain point.

public class Contact : INullable, IBinarySerialize

We have already seen that UDTs need to implement INullable, UserDefined UDTs also need to implement IBinarySerialize.

//<Items that define the UDT>

UserDefined UDTs can have fields of any type, here we are using Strings to define a FirstName and a PersonState. One of the cool things that happen when you define your own serialization is that you can determine which of the fields you want to persist. With Native format UDTs all fields are serialized whether you need them or not. Here PersonState is a field that we don’t care about persisting.

public void Write(BinaryWriter w)

We have come to the part where you manually serialize your UDT, in this case we only care about FirstName so our code is as simple as Write(ing) out the FirstName property. Done.

public void Read(BinaryReader r)

Nothing complicated about the Read method, it needs to be able to get the output of the Write method and restore the state of the UDT. In this case we know that Write stores the data of FirstName so we read the BinaryReader and store the entire thing into FirstName.

//<Items required so that the UDT works>

Take a look at my previous blog for more complete information. These methods are required for all User Defined types. The only interesting concept is that Null and IsNull must match. ToString gives you the string representation of the UDT and Parse is able to create a UDT from an input string.

Rambling out, Standard Disclaimer: This post is provided AS IS and confers no rights. I am likely going to have a number of errors in this post.

Comments

• Anonymous
  July 29, 2004
  ALTER ASSEMBLY - Take Two
• Anonymous
  July 29, 2004
  ALTER ASSEMBLY - Take Two