I spoke at the Dallas .NET User Group meeting last night about the CLR integration in SQL Server 2005. During the Q&A session at the end, I was asked for some more detail about creating overloaded methods and constructors in a User-Defined Types (UDT). Here is some information regarding the topics brought up at that meeting. 

Fact #1: You can create overloaded methods in your UDTs, but keep in mind that...

• Overloaded methods are not directly usable by T-SQL
• However, overloaded methods are directly usable within the type or by a .NET application.

For example, the following can certainly be defined with a UDT

public SqlInt32 AddAB(SqlInt32 A, SqlInt32 B) { return new SqlInt32(AB(A.Value, B.Value)) ; }
public Int32 AB(Int32 A, Int32 B) { return A + B; }
public String AB(String A, String B) { return A + B; }

You cannot call the AB method from within T-SQL, but you can call AddAB which in turn calls the first AB method within the type. You can also use the AB method from a .NET application (data client, for example) that also uses the type.

Fact #2: You can create overloaded constructors in your UDTs, but keep in mind that...

• Constructors with parameters are not directly usable by T-SQL
  • For a reference type (class), T-SQL calls only the parameterless constructor
  • For a value type (struct), T-SQL does not call any constructors
• However, constructors with parameters are directly usable within the type or by a .NET application.

The following code snippet shows two constructors, the Bytes property, and the required Parse method which is used T-SQL to create a new instance of the type and assign an initial value to the instance (from string data). T-SQL does not implement a new operator and thus, Parse allows for the same functionality. The static Parse method in turn uses one of the overloaded constructors for this struct to create and return the instance.

public struct MyType: INullable, IComparable, IBinarySerialize
{
    internal Boolean _isNull;
    internal Byte[] _bytes;

    public MyType (SqlString s)
    {
        this._bytes= s.GetNonUnicodeBytes();
        this._isNull = false;
    }

    public MyType(SqlBytes b)
    {
        this._bytes= new Byte[b.Value.Length];
        Array.Copy(b.Value, this._bytes, b.Value.Length);
        this._isNull = false;
    }

    public static MyType Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        return new MyType(s);
    }  

    public SqlBytes Bytes
    {
        get { return (new SqlBytes(this._bytes)); }
        set
        {
            if (value.IsNull == false)
            this._bytes = value.Value;
        }
    }
    // Other UDT code not shown for space reasons...
}

The constructor with the SqlString parameter is indirectly used by T-SQL (via the Parse method). And in this case (assuming you trust me about the rest of the code), the constructor with the SqlBytes parameter is not used by T-SQL. The following code shows an example of using this type in T-SQL:

declare @mytype MyType
-- Type's Parse method is called here
set @mytype = CAST(0x000000010000000500000256 as varchar(12))
select @mytype.Bytes
--returns 0x000000010000000500000256 as expected

The Parse method only will accept a SqlString as its sole parameter (or the UDT will not work in SQL Server). And so I explicitly cast the varbinary value to varchar. T-SQL, however, will implicitly cast for you (if possible, of course), and so, in this case, the following code also works:

declare @mytype MyType
-- Type's Parse method is called here
set @mytype = 0x000000010000000500000256
select @mytype.Bytes
--returns 0x000000010000000500000256 as expected

Some creative coding, however, will allow you to create static methods that act as new operators. For example, if we add this method into the type:

    public static MyType NewFromBytes(SqlBytes b)
    {
        if (b.IsNull)
            return Null;
        return new MyType(b);
  }

We could now call this code to create the type instance and not worry about any implicit conversion that might occur:

declare @mytype MyType
-- Type's Parse method is never called
set @mytype = MyType::NewFromBytes(0x000000010000000500000256)
select @mytype.Bytes
--returns 0x000000010000000500000256 as expected

Well, I hope that gives you some insight into UDTs in SQL Server and T-SQL.