Lists in User Defined Types (SQL Server 2008)

Question

I'm trying to define a new type and have not had much luck finding any information about using lists within them. Basically my new type will contain two lists, lets say x and y of type SqlSingle (the user defined type is written in C#) is this even possible?

If not how are you supposed to go about simulating a two lists of an arbitary length in an SQL Server 2008 column?

I'm possibly going about this the wrong way but it is the best approach I can think of at the moment. Any help is very much appreciated.

Answer 1

Lists as you describe are usually normalized - that is, stored in separate tables with one row per item - rather than trying to cram them into a single column. If you can share more info on what you are trying to accomplish, maybe we can offer more assistance.

Edit - suggested table structure:

-- route table--
route_id      int   (PK)
route_length  int (or whatever)
route_info    <other fields as needed>

-- waypoint table --
route_id      int      (PK)
sequence      tinyint  (PK)
lat           decimal(9,6)
lon           decimal(9,6)
waypoint_info <other fields as needed>

Answer 2

You can use a List<T> in a CLR UDT - although CLR types are structs, which should be immutable, so a ReadOnlyCollection<T> would be a better choice if you don't have a very compelling reason for the mutability. What you need to know in either case is that SQL won't know how to use the list itself; you can't simply expose the list type as a public IList<T> or IEnumerable<T> and be on your merry way, like you would be able to do in pure .NET.

Typically the way to get around this would be to expose a Count property and some methods to get at the individual list items.

Also, in this case, instead of maintaining two separate lists of SqlSingle instances, I would create an additional type to represent a single point, so you can manage it independently and pass it around in SQL if you need to:

[Serializable]
[SqlUserDefinedType(Format.Native)]
public struct MyPoint
{
    private SqlSingle x;
    private SqlSingle y;

    public MyPoint()
    {
    }

    public MyPoint(SqlSingle x, SqlSingle y) : this()
    {
        this.x = x;
        this.y = y;
    }

    // You need this method because SQL can't use the ctors
    [SqlFunction(Name = "CreateMyPoint")]
    public static MyPoint Create(SqlSingle x, SqlSingle y)
    {
        return new MyPoint(x, y);
    }

    // Snip Parse method, Null property, etc.
}

The main type would look something like this:

[Serializable]
[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = ...)]
public struct MyUdt
{
    // Make sure to initialize this in any constructors/builders
    private IList<MyPoint> points;

    [SqlMethod(OnNullCall = false, IsDeterministic = true, IsPrecise = true)]
    public MyPoint GetPoint(int index)
    {
        if ((index >= 0) && (index < points.Count))
        {
            return points[index];
        }
        return MyPoint.Null;
    }

    public int Count
    {
        get { return points.Count; }
    }
}

If you need SQL to be able to get a sequence of all the points, then you can add an enumerable method to the sequence type as well:

[SqlFunction(FillRowMethodName = "FillPointRow",
    TableDefinition = "[X] real, [Y] real")]
public static IEnumerable GetPoints(MyUdt obj)
{
    return obj.Points;
}

public static void FillPointRow(object obj, out SqlSingle x, out SqlSingle y)
{
    MyPoint point = (MyPoint)obj;
    x = point.X;
    y = point.Y;
}

You might think that it's possible to use an IEnumerable<T> and/or use an instance method instead of a static one, but don't even bother trying, it doesn't work.

So the way you can use the resulting type in SQL Server is:

DECLARE @UDT MyUdt
SET @UDT = <whatever>

-- Will show the number of points
SELECT @UDT.Count

-- Will show the binary representation of the second point
SELECT @UDT.GetPoint(1) AS [Point]

-- Will show the X and Y values for the second point
SELECT @UDT.GetPoint(1).X AS [X], @UDT.GetPoint(1).Y AS [Y]

-- Will show all the points
SELECT * FROM dbo.GetPoints(@UDT)

Hope this helps get you on the right track. UDTs can get pretty complicated to manage when they're dealing with list/sequence data.

Also note that you'll obviously need to add serialization methods, builder methods, aggregate methods, and so on. It can be quite an ordeal; make sure that this is actually the direction you want to go in, because once you start adding UDT columns it can be very difficult to make changes if you realize that you made the wrong choice.