Share via

Coding User-Defined Types 

  • Article

When coding your user-defined type (UDT) definition, you must implement various features, depending on whether you are implementing the UDT as a class or a structure, as well as on the format and serialization options you have chosen.

The example in this section illustrates implementing a Point UDT as a struct (or Structure in Visual Basic). The Point UDT consists of X and Y coordinates which are implemented as property procedures.

The following namespaces are required when defining a UDT:

Imports System
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

The Microsoft.SqlServer.Server namespace contains the objects required for various attributes of your UDT and the System.Data.SqlTypes namespace contains the classes that represent SQL Server native data types available to the assembly. There may of course be additional namespaces that your assembly requires in order to function correctly. The Point UDT also uses the System.Text namespace for working with strings.

Specifying Attributes

Attributes determine how serialization is used to construct the storage representation of UDTs and to transmit UDTs by value to the client.

The SqlUserDefinedTypeAttribute is required. The Serializable attribute is optional; SQL Server does not require it. You can also specify the SqlFacetAttribute to provide information about the return type of a UDT.

Point UDT Attributes

The SqlUserDefinedTypeAttribute sets the storage format for the Point UDT to Native. IsByteOrdered is set to true, which guarantees that the results of comparisons will be the same in SQL Server as if the same comparison had taken place in managed code. The UDT implements the INullable interface to make the UDT null aware.

The following code fragment shows the attributes for the Point UDT.

<Serializable(), SqlUserDefinedTypeAttribute(Format.Native, _
  IsByteOrdered:=True)> _
  Public Structure Point
    Implements INullable

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,
  IsByteOrdered=true)]
public struct Point : INullable
{

Implementing Nullability

In addition to specifying the attributes for your assemblies correctly, your UDT must also support nullability. UDTs loaded into SQL Server are null-aware, but in order for the UDT to recognize a null value, the UDT must implement the INullable interface.

You must create a property named IsNull, which is needed to determine whether a value is null from within CLR code. When SQL Server finds a null instance of a UDT, it is persisted using normal null-handling methods. The server doesn't waste time serializing or deserializing the UDT if it doesn't have to, and it doesn't waste space to store a null UDT. This check for nulls is performed every time a UDT is brought over from the CLR, which means that using the Transact-SQL IS NULL construct to check for null UDTs should always work. The IsNull property is also used by the server to test whether an instance is null. Once the server determines that the UDT is null, it can use its native null handling.

In the following example, the is_Null variable is private and holds the state of null for the instance of the UDT. Your code must maintain an appropriate value for is_Null. The UDT must also have a static property named Null that returns a null value instance of the UDT. This allows the UDT to return a null value if the instance is indeed null in the database.

Private is_Null As Boolean

Public ReadOnly Property IsNull() As Boolean _
   Implements INullable.IsNull
    Get
        Return (is_Null)
    End Get
End Property

Public Shared ReadOnly Property Null() As Point
    Get
        Dim pt As New Point
        pt.is_Null = True
        Return (pt)
    End Get
End Property

private bool is_Null;

public bool IsNull
{
    get
    {
        return (is_Null);
    }
}

public static Point Null
{
    get
    {
        Point pt = new Point();
        pt.is_Null = true;
        return pt;
    }
}

Implementing the Parse Method

The Parse and ToString methods allow for conversions to and from string representations of the UDT. The Parse method allows a string to be converted into a UDT. It must be declared as static (or Shared in Visual Basic), and take a parameter of type SqlString.

The following code implements the Parse method for the Point UDT, which separates out the X and Y coordinates. The Parse method has a single argument of type SqlString, and assumes that X and Y values are supplied as a comma-delimited string. Setting the OnNullCall attribute to false prevents the Parse method from being called from a null instance of Point.

<SqlMethod(OnNullCall:=False)> _
Public Shared Function Parse(ByVal s As SqlString) As Point
    If s.IsNull Then
        Return Null
    End If

    ' Parse input string here to separate out points.
    Dim pt As New Point()
    Dim xy() As String = s.Value.Split(",".ToCharArray())
    pt.X = Int32.Parse(xy(0))
    pt.Y = Int32.Parse(xy(1))
    Return pt
End Function

[SqlMethod(OnNullCall = false)]
public static Point Parse(SqlString s)
{
    if (s.IsNull)
        return Null;

    // Parse input string to separate out points.
    Point pt = new Point();
    string[] xy = s.Value.Split(",".ToCharArray());
    pt.X = Int32.Parse(xy[0]);
    pt.Y = Int32.Parse(xy[1]);
    return pt;
}

Implementing the ToString Method

The ToString method converts the Point UDT to a string value. In this case, the string "NULL" is returned for a Null instance of the Point type. The ToString method reverses the Parse method by using a StringBuilder to return a comma-delimited String consisting of the X and Y coordinate values. Because InvokeIfReceiverIsNull defaults to false, the check for a null instance of Point is unnecessary.

Private _x As Int32
Private _y As Int32

Public Overrides Function ToString() As String
    If Me.IsNull Then
        Return "NULL"
    Else
        Dim builder As StringBuilder = New StringBuilder
        builder.Append(_x)
        builder.Append(",")
        builder.Append(_y)
        Return builder.ToString
    End If
End Function

private Int32 _x;
private Int32 _y;

public override string ToString()
{
    if (this.IsNull)
        return "NULL";
    else
    {
        StringBuilder builder = new StringBuilder();
        builder.Append(_x);
        builder.Append(",");
        builder.Append(_y);
        return builder.ToString();
    }
}

Exposing UDT Properties

The Point UDT exposes X and Y coordinates that are implemented as public read-write properties of type Int32.

Public Property X() As Int32
    Get
        Return (Me._x)
    End Get

    Set(ByVal Value As Int32)
        _x = Value
    End Set
End Property

Public Property Y() As Int32
    Get
        Return (Me._y)
    End Get

    Set(ByVal Value As Int32)
        _y = Value
    End Set
End Property

public Int32 X
{
    get
    {
        return this._x;
    }
    set 
    {
        _x = value;
    }
}

public Int32 Y
{
    get
    {
        return this._y;
    }
    set
    {
        _y = value;
    }
}

Validating UDT Values

When working with UDT data, the engine automatically converts binary values to UDT values. This conversion process involves checking that values are appropriate for the serialization format of the type and ensuring that the value can be deserialized correctly. This prevents invalid values from being persisted in the database. In some cases, this level of checking may be inadequate. Additional validation may be required when UDT values are required to be in an expected domain or range. For example, a UDT that implements a date might require the day value to be a positive number that falls within a certain range of valid values.

The ValidationMethodName property of the SqlUserDefinedTypeAttribute allows you to supply the name of a validation method that the server runs when data is assigned to a UDT or converted to a UDT. ValidationMethodName is also called during bcp, BULK INSERT, DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE, distributed query, and tabular data stream (TDS) remote procedure call (RPC) operations. The default value for ValidationMethodName is null, indicating that there is no validation method.

Example

The following code fragment shows the declaration for the Point class, which specifies a ValidationMethodName of ValidatePoint.

<Serializable(), SqlUserDefinedTypeAttribute(Format.Native, _
  IsByteOrdered:=True, _
  ValidationMethodName:="ValidatePoint")> _
  Public Structure Point

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,
  IsByteOrdered=true, 
  ValidationMethodName = "ValidatePoint")]
public struct Point : INullable
{

If a validation method is specified, it must have a signature that looks like the following code fragment.

Private Function ValidationFunction() As Boolean
    If (validation logic here) Then
        Return True
    Else
        Return False
    End If
End Function

private bool ValidationFunction()
{
    if (validation logic here)
    {
        return true;
    }
    else
    {
        return false;
    }
}

The validation method can have any scope and should return true if the value is valid, and false otherwise. If the method returns false or throws an exception, the value is treated as invalid and an error is raised.

In the example below, the code allows only values of zero or greater the X and Y coordinates.

Private Function ValidatePoint() As Boolean
    If (_x >= 0) And (_y >= 0) Then
        Return True
    Else
        Return False
    End If
End Function

private bool ValidatePoint()
{
    if ((_x >= 0) && (_y >= 0))
    {
        return true;
    }
    else
    {
        return false;
    }
}

Validation Method Limitations

The validation method is called by the server when performing conversions. It is not called when data is inserted by setting individual properties, or when data is inserted using a Transact-SQL INSERT statement.

You must explicitly call the validation method from property setters and the Parse method if you want the validation method to execute in all situations. This is not a requirement, and in some cases may not even be desirable.

Parse Validation Example

To ensure that the ValidatePoint method is invoked in the Point class, you must call it from the Parse method and from the property procedures that set the X and Y coordinate values. The following code fragment shows how to call the ValidatePoint validation method from the Parse function.

<SqlMethod(OnNullCall:=False)> _
Public Shared Function Parse(ByVal s As SqlString) As Point
    If s.IsNull Then
        Return Null
    End If

    ' Parse input string here to separate out points.
    Dim pt As New Point()
    Dim xy() As String = s.Value.Split(",".ToCharArray())
    pt.X = Int32.Parse(xy(0))
    pt.Y = Int32.Parse(xy(1))

    ' Call ValidatePoint to enforce validation
    ' for string conversions.
    If Not pt.ValidatePoint() Then
        Throw New ArgumentException("Invalid XY coordinate values.")
    End If
    Return pt
End Function

[SqlMethod(OnNullCall = false)]
public static Point Parse(SqlString s)
{
    if (s.IsNull)
        return Null;

    // Parse input string to separate out points.
    Point pt = new Point();
    string[] xy = s.Value.Split(",".ToCharArray());
    pt.X = Int32.Parse(xy[0]);
    pt.Y = Int32.Parse(xy[1]);

    // Call ValidatePoint to enforce validation
    // for string conversions.
    if (!pt.ValidatePoint()) 
        throw new ArgumentException("Invalid XY coordinate values.");
    return pt;
}

Property Validation Example

The following code fragment shows how to call the ValidatePoint validation method from the property procedures that set the X and Y coordinates.

Public Property X() As Int32
    Get
        Return (Me._x)
    End Get

    Set(ByVal Value As Int32)
        Dim temp As Int32 = _x
        _x = Value
        If Not ValidatePoint() Then
            _x = temp
            Throw New ArgumentException("Invalid X coordinate value.")
        End If
    End Set
End Property

Public Property Y() As Int32
    Get
        Return (Me._y)
    End Get

    Set(ByVal Value As Int32)
        Dim temp As Int32 = _y
        _y = Value
        If Not ValidatePoint() Then
            _y = temp
            Throw New ArgumentException("Invalid Y coordinate value.")
        End If
    End Set
End Property

    public Int32 X
{
    get
    {
        return this._x;
    }
    // Call ValidatePoint to ensure valid range of Point values.
    set 
    {
        Int32 temp = _x;
        _x = value;
        if (!ValidatePoint())
        {
            _x = temp;
            throw new ArgumentException("Invalid X coordinate value.");
        }
    }
}

public Int32 Y
{
    get
    {
        return this._y;
    }
    set
    {
        Int32 temp = _y;
        _y = value;
        if (!ValidatePoint())
        {
            _y = temp;
            throw new ArgumentException("Invalid Y coordinate value.");
        }
    }
}

Coding UDT Methods

When coding UDT methods, consider whether the algorithm used could possibly change over time. If so, you may want to consider creating a separate class for the methods your UDT uses. If the algorithm changes, you can recompile the class with the new code and load the assembly into SQL Server without affecting the UDT. In many cases UDTs can be reloaded using the Transact-SQL ALTER ASSEMBLY statement, but that could potentially cause problems with existing data. For example, the Currency UDT included with the AdventureWorks sample database uses a ConvertCurrency function to convert currency values, which is implemented in a separate class. It's possible that conversion algorithms may change in the future in unpredictable ways or that new functionality may be required. Separating the ConvertCurrency function from the Currency UDT implementation provides greater flexibility when planning for future changes.

For information on how to install the AdventureWorks CLR samples, see "Installing Samples" in SQL Server Books Online.

Example

The Point class contains three simple methods for calculating distance: Distance, DistanceFrom and DistanceFromXY. Each returns a double calculating the distance from Point to zero, the distance from a specified point to Point, and the distance from specified X and Y coordinates to Point. Distance and DistanceFrom each call DistanceFromXY, and demonstrate how to use different arguments for each method.

' Distance from 0 to Point.
<SqlMethod(OnNullCall:=False)> _
Public Function Distance() As Double
    Return DistanceFromXY(0, 0)
End Function

' Distance from Point to the specified point.
<SqlMethod(OnNullCall:=False)> _
Public Function DistanceFrom(ByVal pFrom As Point) As Double
    Return DistanceFromXY(pFrom.X, pFrom.Y)
End Function

' Distance from Point to the specified x and y values.
<SqlMethod(OnNullCall:=False)> _
Public Function DistanceFromXY(ByVal ix As Int32, ByVal iy As Int32) _
    As Double
    Return Math.Sqrt(Math.Pow(ix - _x, 2.0) + Math.Pow(iy - _y, 2.0))
End Function

// Distance from 0 to Point.
[SqlMethod(OnNullCall = false)]
public Double Distance()
{
    return DistanceFromXY(0, 0);
}

// Distance from Point to the specified point.
[SqlMethod(OnNullCall = false)]
public Double DistanceFrom(Point pFrom)
{
    return DistanceFromXY(pFrom.X, pFrom.Y);
}

// Distance from Point to the specified x and y values.
[SqlMethod(OnNullCall = false)]
public Double DistanceFromXY(Int32 iX, Int32 iY)
{
    return Math.Sqrt(Math.Pow(iX - _x, 2.0) + Math.Pow(iY - _y, 2.0));
}

Using SqlMethod Attributes

The SqlMethodAttribute class provides custom attributes that can be used to mark method definitions in order to specify determinism, on null call behavior, and to specify whether a method is a mutator. Default values for these properties are assumed, and the custom attribute is only used when a non-default value is needed.

The following table describes some of the relevant SqlMethodAttribute properties that can be used in UDT methods, and lists their default values.

SqlMethodAttribute Property Description

DataAccess

Indicates whether the function involves access to user data stored in the local instance of SQL Server. Default is DataAccessKind.None.

IsDeterministic

Indicates whether the function produces the same output values given the same input values and the same database state. Default is false.

IsMutator

Indicates whether the method causes a state change in the UDT instance. Default is false.

IsPrecise

Indicates whether the function involves imprecise computations, such as floating point operations. Default is false.

OnNullCall

Indicates whether the method is called when null reference input arguments are specified. Default is true.

Example

The IsMutator property allows you to mark a method that allows a change in the state of an instance of a UDT. Transact-SQL does not allow you to set two UDT properties in the SET clause of one UPDATE statement. However, you can have a method marked as a mutator that changes the two members.

Note

Mutator methods are not allowed in queries. They can be called only in assignment statements or data modification statements. If a method marked as mutator does not return void (or is not a Sub in Visual Basic), CREATE TYPE fails with an error.

The following statement assumes the existence of a Triangles UDT that has a Rotate method. The following Transact-SQL update statement invokes the Rotate method:

UPDATE Triangles SET t.RotateY(0.6) WHERE id=5

The Rotate method is decorated with the SqlMethod attribute setting IsMutator to true so that SQL Server can mark the method as a mutator method. The code also sets OnNullCall to false, which indicates to the server that the method returns a null reference (Nothing in Visual Basic) if any of the input parameters are null references.

<SqlMethod(IsMutator:=True, OnNullCall:=False)> _
Public Sub Rotate(ByVal anglex as Double, _
  ByVal angley as Double, ByVal anglez As Double) 
   RotateX(anglex)
   RotateY(angley)
   RotateZ(anglez)
End Sub

[SqlMethod(IsMutator = true, OnNullCall = false)]
public void Rotate(double anglex, double angley, double anglez) 
{
   RotateX(anglex);
   RotateY(angley);
   RotateZ(anglez);
}

Implementing a UDT with a User-Defined Format

When implementing a UDT with a user-defined format, you must implement Read and Write methods that implement the IBinarySerialize interface to handle serializing and deserializing UDT data. You must also specify the MaxByteSize property of the SqlUserDefinedTypeAttribute.

The Currency UDT

The Currency UDT is included with the CLR samples that can be installed with SQL Server 2005. For information on how to install the CLR samples, see "Installing Samples" in SQL Server Books Online.

The Currency UDT supports handing amounts of money in a particular culture's monetary system. You must define two fields: a string for CultureInfo, which specifies who issued the currency (en-us, for example) and a decimal for CurrencyValue, the amount of money.

Although it is not used by the server for performing comparisons, the Currency UDT implements the IComparable interface, which exposes a single method, CompareTo. This is used on the client side in situations where it is desirable to accurately compare or order currency values within cultures.

Code running in the CLR compares the culture separately from the currency value. For Transact-SQL code, the following actions determine the comparison:

  1. Set the IsByteOrdered attribute to true, which tells SQL Server to use the persisted binary representation on disk for comparisons.

  2. Use the Write method for the Currency UDT to determine how the UDT is persisted on disk and therefore how UDT values will be compared and ordered for Transact-SQL operations.

  3. Save the Currency UDT using the following binary format:

    1. Save the culture as a UTF-16 encoded string for bytes 0-19 with padding to the right with null characters.

    2. Use bytes 20 and above to contain the decimal value of the currency.

The purpose of the padding is to ensure that the culture is completely separated from the currency value, so that when one UDT is compared against another in Transact-SQL code, culture bytes are compared against culture bytes, and currency byte values are compared against currency byte values.

For the complete code listing for the Currency UDT, follow the directions for installing the CLR samples in "Installing Samples" in SQL Server Books Online.

Currency Attributes

The Currency UDT is defined with the following attributes.

<Serializable(), Microsoft.SqlServer.Server.SqlUserDefinedType( _
    Microsoft.SqlServer.Server.Format.UserDefined, _
    IsByteOrdered:=True, MaxByteSize:=32), _
    CLSCompliant(False)> _
Public Structure Currency
Implements INullable, IComparable, _
Microsoft.SqlServer.Server.IBinarySerialize

[Serializable]
[SqlUserDefinedType(Format.UserDefined, 
    IsByteOrdered = true, MaxByteSize = 32)]
    [CLSCompliant(false)]
    public struct Currency : INullable, IComparable, IBinarySerialize
    {

Creating Read and Write Methods with IBinarySerialize

When you choose UserDefined serialization format, you also must implement the IBinarySerialize interface and create your own Read and Write methods. The following procedures from the Currency UDT use the BinaryReader and BinaryWriter to read from and write to the UDT.

Public Sub Read(ByVal r As System.IO.BinaryReader) _
  Implements Microsoft.SqlServer.Server.IBinarySerialize.Read
    Dim name As Char() = r.ReadChars(cultureNameMaxSize)
    Dim stringEnd As Integer = Array.IndexOf(name, CChar(vbNullChar))

    If stringEnd = 0 Then
        cultureName = Nothing
        Return
    End If

    cultureName = New String(name, 0, stringEnd)
    currencyVal = r.ReadDecimal()
End Sub

' IBinarySerialize methods
' The binary layout is as follow:
'    Bytes 0 - 19: Culture name, padded to the right with null
'    characters, UTF-16 encoded
'    Bytes 20+: Decimal value of money
' If the culture name is empty, the currency is null.
Public Sub Write(ByVal w As System.IO.BinaryWriter) _
  Implements Microsoft.SqlServer.Server.IBinarySerialize.Write
    If Me.IsNull Then
        w.Write(nullMarker)
        w.Write(System.Convert.ToDecimal(0))
        Return
    End If

    If cultureName.Length > cultureNameMaxSize Then
        Throw New ApplicationException(String.Format(CultureInfo.CurrentUICulture, _
           "{0} is an invalid culture name for currency as it is too long.", cultureNameMaxSize))
    End If

    Dim paddedName As String = cultureName.PadRight(cultureNameMaxSize, CChar(vbNullChar))

    For i As Integer = 0 To cultureNameMaxSize - 1
        w.Write(paddedName(i))
    Next i

    ' Normalize decimal value to two places
    currencyVal = Decimal.Floor(currencyVal * 100) / 100
    w.Write(currencyVal)
End Sub

public void Read(System.IO.BinaryReader r)
{
    char[] name = r.ReadChars(cultureNameMaxSize);
    int stringEnd = Array.IndexOf(name, '\0');

    if (stringEnd == 0)
    {
        cultureName = null;
        return;
    }

    cultureName = new String(name, 0, stringEnd);
    currencyValue = r.ReadDecimal();
}

// IBinarySerialize methods
// The binary layout is as follow:
//    Bytes 0 - 19:Culture name, padded to the right 
//    with null characters, UTF-16 encoded
//    Bytes 20+:Decimal value of money
// If the culture name is empty, the currency is null.
public void Write(System.IO.BinaryWriter w)
{
    if (this.IsNull)
    {
        w.Write(nullMarker);
        w.Write((decimal)0);
        return;
    }

    if (cultureName.Length > cultureNameMaxSize)
    {
        throw new ApplicationException(string.Format(
            CultureInfo.InvariantCulture, 
            "{0} is an invalid culture name for currency as it is too long.", 
            cultureNameMaxSize));
    }

    String paddedName = cultureName.PadRight(cultureNameMaxSize, '\0');
    for (int i = 0; i < cultureNameMaxSize; i++)
    {
        w.Write(paddedName[i]);
    }

    // Normalize decimal value to two places
    currencyValue = Decimal.Floor(currencyValue * 100) / 100;
    w.Write(currencyValue);
}
public void Read(System.IO.BinaryReader r)
{
    char[] name = r.ReadChars(cultureNameMaxSize);
    int stringEnd = Array.IndexOf(name, '\0');

    if (stringEnd == 0)
    {
        cultureName = null;
        return;
    }

    cultureName = new String(name, 0, stringEnd);
    currencyValue = r.ReadDecimal();
}

For the complete code listing for the Currency UDT, see "Installing Samples" in SQL Server Books Online.

See Also

Other Resources

Creating a User-Defined Type