SQL Server 2012 : Exploring SQL CLR - SQL CLR Types

The last SQL CLR feature for us to explore is user-defined types (UDTs). This feature is perhaps the most interesting facet of SQL CLR integration, yet also the most controversial. It’s interesting because, technically, it allows for storage of objects in the database. It’s controversial because it’s prone to abuse. SQL CLR types were not implemented to allow developers to create object-oriented databases; they were created to allow multi-value or multi-behavior data types to be stored, retrieved, and easily manipulated.

SQL CLR types have certain indexing limitations, and their entire value must be updated when any of their individual property/field values is updated.

Note

More information about SQL CLR UDTs is available in the MSDN article “Using CLR Integration in SQL Server 2005” by Rathakrishnan et al. You can find this article online at http://msdn.microsoft.com/en-us/library/ms345136.aspx. Although written for SQL Server 2005, it is nonetheless an excellent source of information that is applicable to subsequent versions of SQL Server, including SQL Server 2012.

SQL CLR type methods must be static. You cannot, therefore, call methods from T-SQL as instance methods; instead, you must use a special TypeName::MethodName() syntax. You can implement properties as you would in any conventional class and read from them or write to them from T-SQL using a standard variable.property/column.property dot-separated syntax.

Example 1, the code from struct typPoint in typTest.cs in the sample project, shows the implementation of typPoint, a CLR type that can be used to store Cartesian coordinates in the database.

Example 1. typPoint struct from typTest.cs.

[Serializable]
[SqlUserDefinedType(Format.Native)]
public struct typPoint : INullable
{
    private bool m_Null;
    private double m_x;
    private double m_y;

    public override string ToString()
    {
        if (this.IsNull)
            return "NULL";
        else
            return this.m_x + ":" + this.m_y;
    }

    public bool IsNull
    {
        get
        {
            return m_Null;
        }
    }

    public static typPoint Null
    {
        get
        {
            typPoint pt = new typPoint();
            pt.m_Null = true;
            return pt;
        }
    }

    public static typPoint Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        else
        {
            //Parse input string here to separate out points
            typPoint pt = new typPoint();
            char[] parms = new char[1];
            parms[0] = ':';
            string str = (string)s;
            string[] xy = str.Split(parms);
            pt.X = double.Parse(xy[0]);
            pt.Y = double.Parse(xy[1]);
            return pt;
        }
    }

    public static double Sum(typPoint p)
    {
        return p.X + p.Y;
    }

    public double X
    {
        get { return m_x; }
        set { m_x = value; }
    }

    public double Y
    {
        get { return m_y; }
        set { m_y = value; }
    }

}

Through the class’s X and Y properties, you can process coordinates in a single database column or variable. You can assign coordinate values to an instance of the type as a colon-delimited string—for example, 3:4, by using the Parse method (implicitly). You can then read them back in the same format by using the ToStringX or Y portion by using the separate X and Y properties. The class implements the INullable interface and its IsNull property. The Sum method demonstrates how to expose a static member and allow it to access instance properties by accepting an instance of the SQL CLR type of which it is a member. method. Once a value has been assigned, you can individually read or modify its

Notice that the class is a struct and that the Serializable and SqlUserDefinedType attributes have been applied to it. As with the SqlUserDefinedAggregateSqlUserDefinedType is required by SQL Server and appears in the Class Library sample code as well as the SQL Server Database Project version. As with the SqlUserDefinedAggregate, you simply assign a value of Format.Native to the Format parameter and leave the other parameters unused. attribute,

Note

More Info As with user-defined aggregates, CLR types greater than 8,000 bytes in size must use the Format.UserDefined value for the Format parameter of their special attribute (SqlUserDefinedType).

Example 2, the code from struct typBakersDozen in typTest.cs in the sample project, re-implements the BakersDozen logic used in our aggregate example, this time in a UDT.

Example 2. typBakersDozen struct from typTest.cs.

[Serializable]
[SqlUserDefinedType(Format.Native)]
public struct typBakersDozen : INullable
{
    private bool m_Null;
    private double m_RealQty;

    public override string ToString()
    {
        return (m_RealQty + (long)m_RealQty / 12).ToString();
    }

    public bool IsNull
    {
        get
        {
            return m_Null;
        }
    }

    public static typBakersDozen Null
    {
        get
        {
            typBakersDozen h = new typBakersDozen();
            h.m_Null = true;
            return h;
        }
    }

    public static typBakersDozen Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        else
        {
            typBakersDozen u = new typBakersDozen();
            u.RealQty = double.Parse((string)s);
            return u;
        }
    }

    public static typBakersDozen ParseDouble(SqlDouble d)
    {
        if (d.IsNull)
            return Null;
        else
        {
            typBakersDozen u = new typBakersDozen();
            u.RealQty = (double)d;
            return u;
        }
    }

    public double RealQty
    {
        get { return m_RealQty; }
        set { m_RealQty = value; }
    }

    public double AdjustedQty
    {
        get
        {
            return (m_RealQty + (long)m_RealQty / 12);
        }
        set
        {
            if (value % 12 == 0)
                m_RealQty = value;
            else
                m_RealQty = value - (long)value / 13;
        }
    }

}

The RealQty and AdjustedQty properties allow the ordered quantity to be assigned a value and the adjusted quantity to be automatically calculated, or vice versa. The real quantity is the default “input” value, the adjusted quantity is the default “output” value of the type, and the Parse and ToString methods work accordingly. If the AdjustedQty property is assigned a value that is an even multiple of 12 (which would be invalid), that value is assigned to the RealQty property, forcing the AdjustedQty to be set to its passed value plus its integer quotient when divided by 12.

To deploy the UDTs, you can use attribute-based deployment for the SQL Server Database Project. The script file CreateObjects.sql in the SSMS project supplied with the sample code contains the T-SQL code necessary to deploy the Class Library versions of the UDTs. Here’s the command that deploys typPoint:

CREATE TYPE typPoint
 EXTERNAL NAME SQLCLRDemo.typPoint

The script file TestTypPoint.sql in the SSMS project contains T-SQL code that tests typPoint. Run it and examine the results for an intimate understanding of how to work with the type. The script file CreateTblPoint.sql creates a table with a column that is typed based on typPoint. Run it, and then run the script file TestTblPoint.sql to see how to manipulate tables that use SQL CLR UDTs.

The script file TestTypBakersDozen.sql contains T-SQL code that tests typBakersDozen. The ParseDouble method demonstrates how to implement a non-SqlString parse method. It is named ParseDouble because the Parse method itself cannot be overloaded. You must call ParseDouble explicitly as follows:

DECLARE @t AS dbo.typBakersDozen
SET @t = typBakersDozen::ParseDouble(12)

This is equivalent to using the default Parse method (implicitly) and assigning the string 12 as follows:

DECLARE @t AS dbo.typBakersDozen
SET @t = '12'

Notice that typBakersDozen essentially stores a value for the real quantity, and its properties are really just functions that accept or express that value in its native form or as an adjusted quantity. There is no backing variable for the AdjustedQty property; the get block of the AdjustedQty property merely applies a formula to the backing variable for RealQty and returns the result.

As both these examples show, you should think of CLR UDTs less as objects stored in the database and more as classes that wrap one or a set of scalar values and provide services and conversion functions for manipulating them. This is why Microsoft implemented the geometry and geography data types as SQL CLR UDTs. These types don’t store complex objects, but they do manage entities that cannot be thought of as simple, single values.

More specifically, you should not think of SQL CLR UDTs as object-relational entities. Although it might seem counterintuitive, consider the use of (de)serialization and the xml data type as more appropriate vehicles for storing objects in the database.

We have now investigated all five SQL CLR entities. Before we finish up, we need to discuss CLR assembly security and ongoing maintenance of SQL CLR objects in your databases.