User-Defined Types
User-defined types (UDTs) can be used similarly to normal SQL Server data types to define the type of a column in a table. With older versions of SQL Server, it was already possible to define UDTs. Of course, these UDTs could only be based on SQL types, such as the ZIP type shown in the following code. The stored procedure sp_addtype allows you to create user-defined types. Here the user-defined type ZIP is based on the CHAR data type with a length of 5. NOT NULL specifies that NULL is not allowed with the ZIP data type. By using ZIP as a data type, it is no longer necessary to remember that it should be 5 char long and not null:
EXEC sp_addtype ZIP 'CHAR(5)', 'NOT NULL' With SQL Server 2005, UDTs can be defined with CLR classes. However, this feature is not meant to add object orientation to the database; for example, to create a Person class to have a Person data type. SQL Server is a relational data store, and this is still true with UDTs. You cannot create a class hierarchy of UDTs, and it is not possible to reference fields or properties of a UDT type with a SELECT statement. If properties of a person (for example, Firstname or Lastname) must be accessed or a list of Person objects must be sorted (for example, by Firstname or Lastname), it is still better to define columns for first name or last name inside a Persons table or to use the XML data type.
UDTs are meant for very simple data types. Before .NET, it was also possible to create custom data types; for example, the ZIP data type. With UDTs it is not possible to create a class hierarchy, and they are not meant to get complex data types to the database. One requirement of a UDT is that it must be convertible to a string, because the string representation is used to display the value.
How the data is stored within SQL Server can be defined: either an automatic mechanism can be used to store the data in a native format, or you can convert the data to a byte stream to define how the data should be stored.
Creating UDTs
Next, we will look at how to create a user-defined type with Visual Studio 2005. You create a SqlCoordinate type representing the world coordinates longitude and latitude for easily defining the location of places, cities, and the like. By using the Visual Studio 2005 database project and the User-Defined Types template, and naming the type SqlCoordinate, the base functionality of a custom type is already defined:
using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; [Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)] public struct SqlCoordinate : INullable { public override string ToString() { // Replace the following code with your code return ""; } public bool IsNull { get { // Put your code here return m_Null; } } public static SqlCoordinate Null { get { SqlCoordinate h = new SqlCoordinate(); h.m_Null = true; return h; } } public static SqlCoordinate Parse(SqlString s) { if (s.IsNull) return Null; SqlCoordinate u = new SqlCoordinate(); // Put your code here return u; } // This is a place-holder method public string Method1() { //Insert method code here return "Hello"; } // This is a place-holder static method public static SqlString Method2() { // Insert method code here return new SqlString("Hello"); } // This is a placeholder field member public int var1; // Private member private bool m_Null; } As this type can also be used directly from client code, it is a good idea to add a namespace, which is not done automatically.
The struct SqlCoordinate implements the interface INullable. The interface INullable is required for UDTs because database types can also be null. The attribute [SqlUserDefinedType] is used for automatic deployment with Visual Studio for UDTs. The argument Format.Native defines the serialization format to be used. Two serialization formats are possible: Format.Native and Format.UserDefined .Format.Native is the simple serialization format where the engine performs serialization and deserial-ization of instances. This serialization allows only blittable data types. With the Coordinate class, the data types to serialize are of type int and bool, which are blittable data types. A string is not a blittable data type. Using Format.UserDefined requires the interface IBinarySerialize to be implemented. The IBinarySerialize interface provides custom implementation for user-defined types. Read() and Write() methods must be implemented for serialization of the data to a BinaryReader and a BinaryWriter.
| Tip | Blittable data types have the same memory representation in both managed and unmanaged memory. Conversion is not needed with blittable data types. Blittable data types are byte, sbyte, short, ushort, int, uint, long, ulong, and combinations of these data types such as arrays and structs that only contain these data types. |
[Serializable] [SqlUserDefinedType(Format.Native)] public struct SqlCoordinate : INullable { private int longitude; private int latitude; private bool isNull; The attribute [SqlUserDefinedType] allows setting several properties, which are shown in the follow-ing table.
| SqlUserDefinedTypeAttribute Property | Description |
|---|---|
| Format | The property Format defines how the data type is stored within SQL Server. Currently supported formats are Format.Native and Format.UserDefined. |
| IsByteOrdered | If the property IsByteOrdered is set to true, it is possible to create an index for the data type, and it can be used with GROUP BY and ORDER BY SQL statements. The disk representation will be used for binary comparisons. Each instance can only have one serialized representation, so binary comparisons can succeed. The default is false. |
| IsFixedLength | If the disk representation of all instances is of the same size, IsFixedLength can be set to true. |
| MaxByteSize | The maximum number of bytes needed to store the data is set with MaxByteSize. This property is specified only with a user-defined serialization. |
| Name | With the Name property, a different name of the type can be set. By default the name of the class is used. |
| ValidationMethodName | With the ValidationMethodName property a method name can be defined to validate instances when the deserialization takes place. |
Open table as spreadsheet To represent the direction of the coordinate, the enumeration Orientation is defined:
public enum Orientation { NorthEast, NorthWest, SouthEast, SouthWest } This enumeration can only be used within methods of the struct Coordinate, not as a member field because enumerations are not blittable. Future versions may support enums with the native format in SQL Server.
The struct Coordinate specifies some constructors to initialize the longitude, latitude, and isNull variables. The variable isNull is set to true if no values are assigned to longitude and latitude, which is the case in the default constructor. A default constructor is needed with UDTs.
With the worldwide coordination system, longitude and latitude are defined with degrees, minutes, and seconds. Vienna, Austria has the coordinates 48° 14’ longitude and 16° 20’ latitude. The symbols °, ‘, and “ represent degrees, minutes, and seconds, respectively.
With the variables longitude and latitude, the longitude and latitude values are stored using seconds. The constructor with seven integer parameters converts degrees, minutes, and seconds to seconds, and sets the longitude and latitude to negative values if the coordinate is based in the South or West:
public SqlCoordinate(int longitude, int latitude) { isNull = false; this.longitude = longitude; this.latitude = latitude; } public SqlCoordinate(int longitudeDegrees, int longitudeMinutes, int longitudeSeconds, int latitudeDegrees, int latitudeMinutes, int latitudeSeconds, Orientation orientation) { isNull = false; this.longitude = longitudeSeconds + 60 * longitudeMinutes + 3600 * longitudeDegrees; this.latitude = latitudeSeconds + 60 * latitudeMinutes + 3600 * latitudeDegrees; switch (orientation) { case Orientation.SouthWest: longitude = -longitude; latitude = -latitude; break; case Orientation.SouthEast: longitude = -longitude; break; case Orientation.NorthWest: latitude = -latitude; break; } } The INullable interface defines the property IsNull, which must be implemented to support nullability. The static property Null is used to create an object that represents a null value. In the get accessor a Coordinate object is created, and the isNull field is set to true:
public bool IsNull { get { return isNull; } } public static SqlCoordinate Null { get { SqlCoordinate c = new SqlCoordinate(); c.isNull = true; return c; } } A UDT must be converted from and to a string. For conversion to a string, the ToString() method of the Object class must be overridden. The variables longitude and latitude are converted below for a string representation to show the degrees, minutes, and seconds notation:
public override string ToString() { if (this.isNull) return null; char northSouth = longitude > 0 ? 'N' : 'S'; char eastWest = latitude > 0 ? 'E' : 'W'; int longitudeDegrees = Math.Abs(longitude) / 3600; int remainingSeconds = Math.Abs(longitude) % 3600; int longitudeMinutes = remainingSeconds / 60; int longitudeSeconds = remainingSeconds % 60; int latitudeDegrees = Math.Abs(latitude) / 3600; remainingSeconds = Math.Abs(latitude) % 3600; int latitudeMinutes = remainingSeconds / 60; int latitudeSeconds = remainingSeconds % 60; return String.Format("{0}°{1}'{2}\"{3},{4}°{5}'{6}\"{7}", longitudeDegrees, longitudeMinutes, longitudeSeconds, northSouth, latitudeDegrees, latitudeMinutes, latitudeSeconds, eastWest); } The string that is entered from the user is represented in the SqlString parameter of the static method Parse(). First, the Parse() method checks if the string represents a null value, in which case the Null property is invoked to return an empty Coordinate object. If the SqlString s does not represent a null value, the text of the string is converted to pass the longitude and latitude values to the Coordinate constructor:
public static SqlCoordinate Parse(SqlString s) { if (s.IsNull) return SqlCoordinate.Null; try { string[] coordinates = s.Value.Split(','); char[] separators = { '°', '\'', '\"' }; string[] longitudeVals = coordinates[0].Split(separators); string[] latitudeVals = coordinates[1].Split(separators); Orientation orientation; if (longitudeVals[3] == "N" && latitudeVals[3] == "E") orientation = Orientation.NorthEast; else if (longitudeVals[3] == "S" && latitudeVals[3] == "W") orientation = Orientation.SouthWest; else if (longitudeVals[3] == "S" && latitudeVals[3] == "E") orientation = Orientation.SouthEast; else orientation = Orientation.NorthWest; return new SqlCoordinate( int.Parse(longitudeVals[0]), int.Parse(longitudeVals[1]), int.Parse(longitudeVals[2]), int.Parse(latitudeVals[0]), int.Parse(latitudeVals[1]), int.Parse(latitudeVals[2]), orientation); } catch (Exception ex) { throw new ArgumentException( "Argument has a wrong syntax. " + "This syntax is required: 37°47\'0\"N,122°26\'0\"W", ex.Message); } }
Using UDTs
After building the assembly, it can be deployed with SQL Server 2005. Configuration of the UDT in SQL Server 2005 can either be done with Visual Studio 2005 using the Build 
Deploy Project menu or using these SQL commands:
CREATE ASSEMBLY SampleTypes FROM 'c:\ProCSharp\SQL2005\PropCSharp.SqlTypes.dll' CREATE TYPE Coordinate EXTERNAL NAME [ProCSharp.SqlTypes].[ProCSharp.SqlTypes.SqlCoordinate] With EXTERNAL NAME, the name of the assembly as well as the name of the class, including the namespace, must be set.
Now, it is possible to create a table called Cities that contains the data type SqlCoordinate, as shown in Figures 27-2 and 27-3.
Figure 27-2
Figure 27-3
Using UDTs from Client-Side Code
The assembly of the UDT must be referenced to use the UDT from client-side code. Then it can be used like any other type on the client.
| Important | As the assembly containing the UDTs is used both from the client and from the SQL server, it is a good idea to put UDTs in a separate assembly from the other SQL Server 2005 extensions such as stored procedures and functions. |
In the sample code, the SELECT statement of the SqlCommand object references the columns of the Cities table that contains the Location column, which is of type SqlCoordinate. Calling the method ToString() invokes the ToString() method of the SqlCoordinate class to display the coordinate value in a string format:
using System; using System.Data; using System.Data.SqlClient; class Program { static void Main() { string connectionString = @"server=(local);database=ProCSharp;trusted_connection=true"; SqlConnection connection = new SqlConnection(connectionString); SqlCommand command = connection.CreateCommand(); command.CommandText = "SELECT Id, Name, Location FROM Cities"; connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { Console.WriteLine("{0}: {1}", reader[1].ToString(), reader[2].ToString()); } reader.Close(); } } Of course, it is also possible to cast the returned object from the SqlDataReader to a SqlCoordinate type for using any other implemented methods of the Coordinate type:
SqlCoordinate coordinate = (SqlCoordinate)reader[2]; With all the great functionality of UDTs, you have to be aware of an important restriction. Before deploying a new version of an UDT, the existing version must be dropped. This is only possible if all columns using the type are removed. Don’t plan on using UDTs for types that you change frequently.
User-Defined Aggregates
An aggregate is a function that returns a single value based on multiple rows. Examples of built-in aggregates are COUNT, AVG, and SUM. COUNT returns the record count of all selected records, AVG returns the average of values from a column of selected rows, and SUM returns the sum of all values of a column. All built-in aggregates work only with built-in value types.
A simple usage of the built-in aggregate AVG is shown here to return the average unit price of all products from the AdventureWorks sample database by passing the ListPrice column to the AVG aggregate in the SELECT statement:
SELECT AVG(ListPrice) AS 'average list price' FROM Production.Product The result from the SELECT gives the average list price of all products:
average list price 438,6662
The SELECT statement just returns a single value that represents the average of all ListPrice column values. Aggregates can also work with groups. In the next example, the AVG aggregate is combined with the GROUP BY clause to return the average list price of every product line:
SELECT ProductLine, AVG(ListPrice) AS 'average list price' FROM Production.Product GROUP BY ProductLine The average list price is now grouped by the product line:
ProductLine average list price NULL 16,8429 M 827,0639 R 965,3488 S 50,3988 T 840,7621
For custom value types, and if you want to do a specific calculation based on a selection of rows, you can create a user-defined aggregate.
Creating User-Defined Aggregates
To write a user-defined aggregate with CLR code, a simple class with the methods Init(), Accumulate(), Merge(), and Terminate() must be implemented. The functionality of these methods is shown in the following table.
| UDT Method | Description |
|---|---|
| Init() | The Init() method is invoked for every group of rows to be processed. In this method, initialization can be done for calculation of every row group. |
| Accumulate() | The method Accumulate() is invoked for every value in all groups. The parameter of this method must be of the correct type that is accumulated; this can also be the class of a user-defined type. |
| Merge() | The method Merge() is invoked when the result of one aggregation must be combined with another aggregation. |
| Terminate() | After the last row of every group is processed, the method Terminate() is invoked. Here, the result of the aggregate must be returned with the correct data type. |
The code sample shows how to implement a simple user-defined aggregate to calculate the sum of all rows in every group. For deployment with Visual Studio, the attribute [SqlUserDefinedAggregate] is applied to the class SampleSum. As with the user-defined type, with user-defined aggregates the format for storing the aggregate must be defined with a value from the Format enumeration. Again, Format.Native is for using automatic serialization with blittable data types.
The variable sum is used for accumulation of all values of a group. In the Init() method, the variable sum is initialized for every new group to accumulate. The method Accumulate(), which is invoked for every value, adds the value of the parameter to the sum variable. With the Merge() method, one aggregated group is added to the current group. Finally, the method Terminate() returns the result of a group:
[Serializable] [SqlUserDefinedAggregate(Format.Native)] public struct SampleSum { private int sum; public void Init() { sum = 0; } public void Accumulate(SqlInt32 Value) { sum += Value.Value; } public void Merge(SampleSum Group) { sum += Group.sum; } public SqlInt32 Terminate() { return new SqlInt32(sum); } } | Tip | You can use the Aggregate template from Visual Studio to create the core code for building the user-defined aggregate. The template from Visual Studio creates a struct that uses the SqlString type as parameter and return type with the Accumulate and Terminate methods. You can change the type to a type that represents the requirement of your aggregate. In the example, the SqlInt32 type is used. |
Using User-Defined Aggregates
The user-defined aggregate can be deployed either with Visual Studio 2005 or with the CREATE AGGRE GATE statement:
CREATE AGGREGATE [SampleSum] (@value int) RETURNS [int] EXTERNAL NAME [Demo].[SampleSum] After the user-defined aggregate has been installed, it can be used as shown in the following SELECT statement, where the number of ordered products is returned by joining the Product and PurchaseOrderDetail tables. For the user-defined aggregate, the OrderQty column of the Order PurchaseOrderDetail table is defined as an argument:
SELECT Purchasing.PurchaseOrderDetail.ProductID AS Id, Production.Product.Name AS Product, dbo.SampleSum(Purchasing.PurchaseOrderDetail.OrderQty) AS Sum FROM Production.Product INNER JOIN Purchasing.PurchaseOrderDetail ON Purchasing.PurchaseOrderDetail.ProductID = Production.Product.ProductID GROUP BY Purchasing.PurchaseOrderDetail.ProductID, Production.Product.Name ORDER Id An extract of the returned result that shows the number of orders for products by using the aggregate function SampleSum is presented here:
Id Product Sum 1 Adjustable Race 154 2 Bearing Ball 150 4 Headset Ball Bearings 153 317 LL Crankarm 44000 318 ML Crankarm 44000 319 HL Crankarm 71500
