Managed Code @ SQL Server – Part 2: User-defined Types

In the previous post of this series I covered how to write stored procedures in .NET (“managed stored procedures”) that will execute on an instance of CLR hosted at SQL Server. In this post I’ll cover another feature of using the CLR with SQL Server that you might find more useful: the definition of custom types.

A user-defined type (UDT) can be either a reference type or a value type (class or struct, in C#) and has to be decorated with the SqlUserDefinedType attribute. In addition, a UDT has the following requirements, which are validated when the type is deployed to SQL Server:

  • Be serializable, either with native or custom serialization, so that its instances can be stored on disk;
  • Implement the INullable interface in order to determinate if an instance represents (or not) the SQL NULL value;
  • Have a default constructor (no arguments) to construct instances when reading from disk;
  • Have a static property named Null, used to get a “NULL-representative” instance;
  • Have a static method named Parse, used to build an instance of the type from a string representation;
  • Override the ToString method, accordingly to the Parse method.

Given these requirements, it’s time to define a type. Lets say we want to have rectangles that are represented by strings like “(width, height)”. We define a type named Rectangle, which base structure might be:

[SqlUserDefinedType(Format.Native, Name = "Rectangle")]
public struct Rectangle : INullable
    private const string NULL_STR = "null";


    // INullable member
    public bool IsNull { get; private set; }

    static readonly Rectangle nullInstance = new Rectangle() { IsNull = true };
    public static Rectangle Null
            return nullInstance;

    public override string ToString(){…}

    public static Rectangle Parse(SqlString s){…}

About now our type doesn’t have much of a rectangle, besides the name. If you think from a usage perspective, you would want to be able to select the dimensions of the rectangles that are stored in some table of your database. So, lets add Width and Height properties. These will actually allow us to do something like select r.Width, r.Height from table! Having the dimensions we can also complete the Parse and ToString methods. (In the Parse method I used a regular expression to match the string representation. To keep it simple the rectangle dimensions are integers).

    private static readonly Regex regExpr = new Regex(@"(\((?<w>[1-9]+)\,(?<h>[1-9]+)\))");

    public int Width { get; set; }
    public int Height { get; set; }

    public override string ToString()
        return this.IsNull ? NULL_STR : String.Format("({0},{1})", Width, Height);

    public static Rectangle Parse(SqlString s)
        if (s.IsNull || s.Value.ToLower().Equals(NULL_STR))
            return Null;

        Match m = regExpr.Match(s.Value, 0);
        if (!m.Success) throw new FormatException("Invalid format on input string");

        int w, h;
        w = int.Parse(m.Groups["w"].Value);
        h = int.Parse(m.Groups["h"].Value);
        return new Rectangle() { Width = w, Height = h };

Now we just need to load the type into SQL Server and use it! Again, to do that you can deploy through Visual Studio or use the appropriate T-SQL statements:

CREATE TYPE Rectangle external name CLRSQLSample.Rectangle

Using the new type couldn’t be simpler:

create table rectanglesTable
    r Rectangle

insert into rectanglesTable values('(1,2)')
insert into rectanglesTable values('(3,4)')

select r.Width as width, r.Height as heigth
from rectanglesTable

select cast(r as varchar(5))
from rectanglesTable

select r
from rectanglesTable

It’s important to note that, even though we defined Rectangle in an object oriented fashion, we’re not turning SQL Server into an object oriented database. It’s all just bit streams with a user-defined meaning on top. This becomes clear if you execute the last select statement:

Another point to notice is the definition of other methods on the UDT. First of all, we’re defining a data type; we shouldn’t have any logic other than manipulating the data. Nevertheless, it can be useful to define some methods. Suppose that we wanted to grow our Rectangle’s size with some width and height factors. We can define a method, lets say GrowSize, that gets those two factors as input and adjusts the rectangle dimensions. Methods that you want to use to update the type’s internal state must return void and be decorated with the SqlMethod attribute having its IsMutator property set to true. If not, you wont be able to use them. By the way, properties’ setters are always considered mutators.

[SqlMethod(IsMutator = true)]
public void GrowSize(int wFactor, int hFactor)
    Width *= wFactor; Height *= hFactor;
update rectanglesTable set r.GrowSize(2,2)
select r.Width, r.Height from rectanglesTable

On the other hand, you can also define methods to be used in queries. An example would be a method to calculate the rectangle’s area. Naturally, these methods have to return some value, which makes it impossible to use them as mutators.

And that’s it for user-defined types. As a final note, the usage of UDT is not restricted to tables; you can also use them on stored procedures (managed or unmanaged) and so on. On the next (and last) post of this series I’ll cover another aspect that comes out of UDT. What if you want to do something like select MAX( r ) from rectanglesTable?


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s