[Editor's Update - 11/24/2004: Some of the TSQL syntax used in this article has changed since Beta 1 of SQL Server 2005. Please see the SQL Server Developer Center for general information on this feature in SQL Server 2005 Beta 2 and subsequent releases.]

Types in Yukon

Managed UDTs Let You Extend the SQL Server Type System

Peter W. DeBetta

Code download available at:UDTsinYukon.exe(112 KB)

This article was based on Beta 1 of Microsoft SQL Server Code Name "Yukon" and all information contained herein is subject to change.

Note: This document was developed prior to the product's release to manufacturing and, as such, we cannot guarantee that any details included herein will be exactly the same as those found in the shipping product. The information represents the product at the time this document was printed and should be used for planning purposes only. Information subject to change at any time without prior notice.

SUMMARY

The next version of SQL Server, code-named "Yukon," will offer enhanced support for user-defined types (UDTs). Because UDTs can be managed by the CLR, you can represent a wide variety of data structures to create types not possible with previous versions of SQL Server. With UDTs you can more precisely control the kind of data your type accepts, resulting in better data management. This article explores UDTs in "Yukon" and covers their design and implementation.

Contents

Opaque or Abstract Types
Creating the Assembly
Class Attributes
Comparing UDT Values
Nullability
Supporting Conversion
Other Requirements
Using a UDT in the SQL Server Create Assembly
Create Type
Using a UDT
Dropping and Altering UDTs and Assemblies
Conclusion

User-defined types (UDTs) are one of the new common language runtime (CLR) integration features in the next version of SQL Server™, code-named "Yukon." UDTs in Yukon represent a significant advance from previous versions of SQL Server. SQL Server 2000, for example, supports alias types, which provide a simple way for users to redefine native types. Alias types are created using an existing data type that was defined the same way in multiple locations (tables, procedures, and so on). For example, a ZIP code could be used in multiple tables and as a parameter in multiple stored procedures. You could create a type named ZIP in the following manner:

EXEC sp_addtype ZIP, 'CHAR(5)', 'NOT NULL'

You can then use ZIP instead of char(5) NOT NULL wherever a ZIP code was needed and not have to worry about making it consistent with other fields in other tables that also store ZIP codes. Note that ZIP has an additional constraint on it—NOT NULL. This forces the field to require a value, so wherever this type is used, it will not allow null values. You can create rules and default objects and bind them to the alias type, further enforcing and maintaining data integrity. For example, you can create a rule that only allows numeric values and then bind it to the ZIP type. This technique has some great design and implementation benefits, such as more organized and consistent data structures that will result in more consistent data.

The sp_addtype system stored procedure is being replaced in Yukon by the new Data Description Language (DDL) syntax CREATE TYPE. The example I showed earlier might be written to look something like this:

CREATE TYPE ZIP FROM char(5) NOT NULL

Yukon UDTs allow users to write Microsoft® .NET Framework classes that can be registered to act as scalar types within the SQL language-type system. This puts Yukon UDTs on the same footing as any other SQL native type. Yukon UDTs are compiled into .NET assemblies that can be registered and stored inside the database. Once the assembly is stored in the database, a user can define a class in that assembly as a type using an extension of the CREATE TYPE statement, which I'll cover in more detail later in this article. For a .NET Framework class to act as a type in the SQL type system, it must implement a contract—a set of interfaces and methods—which is verified by Yukon at CREATE TYPE time. Defining and implementing UDTs defined in .NET assemblies allows for more flexible type design that can not only store structured types, but that can also allow custom methods, properties, and serializations on the data.

There are several benefits to this functionality. It's a powerful way to extend the scalar type system of the database. This extensibility mechanism is pretty robust; you can use it to store instances of the type in the database and operate on them in many contexts—as variables, parameters to stored procedures, and values to functions—and use them in almost all the places in which you can use a native type, including in advanced scenarios such as replication, bulk copy, distributed queries, and cross-database operations. In addition, you can invoke behaviors defined on the type as part of your query and even index the results of calling such behaviors to speed up query execution.

Another benefit of UDTs is encapsulation. The state for the type and the operations on the type are always available as a single unit. Methods on the type control access to the state, which frees you to think about your types using familiar OO paradigms and produce reusable components that can be consumed by other application developers. Later in this article I'll discuss the various aspects of implementing UDTs in .NET code and using the resulting classes as data types in Yukon.

Opaque or Abstract Types

Yukon UDTs can be regarded as opaque or abstract data types since users of the type remain unaware of the internal details of the type's implementation. They interact with the type only through its public interface. This new technique of defining UDTs opens new doors in database design. Since UDTs can be managed types with properties, methods, and so on, you can now create types to represent a myriad of data structures previously not possible with SQL Server. Using UDTs, you can create data structures to represent geospatial (location and plat type data), custom binary data, encoded data, and encrypted data.

You can extend the SQL type system by creating a class in managed code that implements the contract for UDTs. You then load the compiled assembly that contains your UDT into a database on the server using the CREATE ASSEMBLY statement and create a type in the database using the CREATE TYPE statement that exposes the managed code UDT.

At this point, you can use the type in a table definition or in variable declarations. Let's start out by examining the managed code requirements for a UDT.

Creating the Assembly

In order to create an assembly in .NET that will be usable in Yukon, there are a few requirements. I will not cover the basic aspects of creating a .NET assembly here. For more information on that, see one of the Microsoft .NET tutorials.

You will be utilizing several namespaces used in UDT development. In order to make your development efforts easier, add directives for the following:

using System.Data.Sql; using System.Data.SqlTypes;

The System.Data.SqlTypes namespace contains classes representing the SQL Server native data types that can be used in your assembly. The System.Data.Sql namespace contains the objects needed for various attributes required in an assembly that will be used as a UDT. All of these types live in System.Data.dll.

Let's look at CLR specifics for creating an assembly that is used by Yukon. In the following sections, I will examine various implementation requirements, some of which will be familiar to you if you have been programming in .NET, and some of which are particular to the new namespaces I just mentioned.

Class Attributes

For Beta 1, two attributes must be specified in order for a class to be used as a UDT: Serializable and SqlUserDefinedType (shown in Figure 1). The Serializable attribute enables the class's data to be serialized by the CLR into a storable format, while SqlUserDefinedType defines UDT features such as the serialization format and the maximum size of the stored structure. The SqlUserDefinedType attribute has four properties (see Figure 2), of which only one is required. In future betas, marking the UDT class as Serializable will no longer be required.

Figure 2 The SQLUserDefinedType Attribute

Figure 1** Class Attributes **

The three serialization formats available in Format.SerializationFormat are: UserDefined, Native, and SerializedDataWithMetadata. As its name suggests, UserDefined serialization is user defined and must be implemented in assembly code. For example, an assembly could do some custom binary serialization. In order to do this, the class must implement the IBinarySerialize interface, specifically its Write and Read methods. This is the most flexible serialization method and can be just as fast as the Native format (depending on the implementation of the Write and Read methods).

The Native format uses native SQL Server binary serialization. It's the fastest, but also the least flexible. You can only use this serialization format if the public properties of the class are fixed length, value type data types. For example, if the properties are of numeric or date/time types, Native can be used. If, however, you expose a string value property, you cannot use the Native format.

SerializedDataWithMetadata uses .NET serialization. It's very flexible because the serialization is handled automatically and is available for more data types, including strings and reference types. Unfortunately, it's also the slowest, performing at an order of magnitude slower than the Native format. More importantly, SerializedDataWithMetadata will not be supported in future betas.

As such, Microsoft recommends that SerializedDataWithMetadata not be used for any new code. If you're already using it, it should only be used in development and never in production. Classes created with SerializedDataWithMetadata should be converted to UserDefined format and should implement IBinarySerialize, using the Read and Write methods to define the custom serialization of the public class members.

Here is an example of these class attributes that would satisfy the minimal requirements for a UDT:

// C# [Serializable] [SqlUserDefinedType (Format.Native)] ' Visual Basic .NET <Serializable(), SqlUserDefinedType(Format.Native)>

As you can see, I am using the two required attributes, and for the SqlUserDefinedType attribute, I am setting its required property.

Comparing UDT Values

The IsByteOrdered property affects how SQL Server uses the instance of a UDT when performing binary comparisons. In other words, if IsByteOrdered is false, the binary representation of the UDT is not comparable with another value. This means that the field defined as this UDT cannot participate in operations that require comparison, such as sorting and indexing. Because it cannot be used in an index, the field cannot be a primary or unique key, which in turn means it cannot participate in referential integrity. It also cannot be used in an ORDER BY, GROUP BY, or PARTITION BY clause.

When IsByteOrdered is true, SQL Server uses the binary representation of the UDT for all comparisons without the need to create a managed instance of the UDT. This allows for the features I just mentioned as well as others including indexability, primary and foreign keys, check and unique constraints, ORDER BY, GROUP BY, and PARTITION BY clauses, ordered comparisons, and comparison operators.

If you need any of these abilities for your UDT, you must set IsByteOrdered equal to true. The one caveat, however, is that IsByteOrdered must only be set to true if the UDT author can guarantee that the serialized binary data is exactly the same as the semantic ordering of the information. Let's take a look at an example to better understand this.

If the Point class represents a point in space, with X and Y (both defined as Int32) as its only properties, would it be a candidate for binary ordering? In other words, can any one Point be thought of as less than or greater than another Point? For example, in comparing PointA (4, -1) to PointB (2, 3), which of these is greater than the other? There is no simple answer to this, just as there is no simple way to store binary data to reflect this ordering. Thus, Point is not a class that would implement binary ordering.

Yukon supports comparison operators (and related features that rely on comparison support like GROUP BY and ORDER BY) on values of a UDT only if the serialized representation of the value is binary ordered. The UDT author indicates that the serialized representation has this property by setting IsByteOrdered to true in the type definition. The ordering of the binary representation of the data must also be equivalent to the semantic order for the type. In other words, performing a comparison operation on the serialized bytes will produce the same result as performing the comparison in managed code.

You should note that in the Native format, SQL Server ensures that the binary representation is comparable if this property is set. In the UserDefined format, it is the responsibility of the developer to pick a normalization algorithm that has this property. Also, all three serialization formats support the equal to (=) and not equal to (!=) comparison operators. The Native and UserDefined serialization formats additionally support the greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=) comparison operators.

Nullability

All Yukon UDTs are null-aware, but in order for the UDT to recognize null as a valid value, the class must implement the INullable interface. This is done as follows:

// C# public class MyClass: INullable ' Visual Basic .NET Public Class MyClass Implements INullable

INullable contains a single property, IsNull, that the class must implement, like so:

// C# public bool IsNull { get {return is_Null;} } ' Visual Basic .NET Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull Get Return (is_Null) End Get End Property

The is_Null variable is private and holds the state of null for the instance. Finally, the class must have a static property called 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, as shown in the code in Figure 3.

Figure 3 Null Values

// C# public static MyClass Null { get { MyClass MC = new MyClass(); MC.is_Null = true; return (MC); } } ' Visual Basic .NET Public Shared ReadOnly Property Null() As MyClass Get Dim MC As New MyClass MC.is_Null = True Return (MC) End Get End Property

Supporting Conversion

The UDT must support conversion to and from a string. To do this, the class needs two public methods defined in its interface. The first is a static method, Parse, which allows a string value to be converted into a UDT instance. Here is the function definition:

// C# public static MyClass Parse(SqlString s) ' Visual Basic .NET Public Shared Function Parse(ByVal s As SqlString) As MyClass

The second method is an override method of the instance method ToString, as shown here:

// C# public override string ToString() ' Visual Basic .NET Public Overrides Function ToString() As String

This method allows a UDT to be converted into a string value. Although not enforced, a proper implementation of these two should be inverses of each other. That is, these two methods should allow a string to be converted into a UDT using Parse and then back to the original string value when converted back using the ToString method.

Other Requirements

In addition to the features I've just mentioned, there are a few other requirements you should be aware of. Since a class needs a constructor, you need to implement one in a UDT. A UDT class requires a zero-argument public constructor, like this:

// C# public MyClass () { }

Additionally, you can create other public constructors. This is the only time when a public member of a type can be overloaded. The following is a list of other limitations you should be aware of when creating your UDT:

• No modifiable static data members are allowed in your .NET code.
• Fixed-length string and fixed-length binary data types are not supported.
• All class, method, and property names must conform to the Yukon sysname system data type, meaning that your public names can be no longer than 128 characters.
• Inheritance is not supported; Yukon cannot utilize inheritance, although it can still exist in the .NET code.

Note that the SQL type system is not aware of the inheritance hierarchy among UDTs. However, UDT authors can use inheritance as an implementation mechanism in the way they structure their classes. You cannot call inherited methods from T-SQL, although you can call such methods in the managed implementation of the type.

You should also be forewarned that if you do create an overloaded method, it will not be caught when you register your assembly or even create your type in SQL Server. This is because the detection of an overloaded method occurs at run time, not when the type is created. Thus, overloaded methods can exist in the class as long as it is not invoked. If the class is invoked, an error will be raised that states: "More than one method, property, or field was found with name method_name in class class_name in assembly assembly_name." Overloaded methods, properties, or fields are not supported. Also, note that there is an exception to this rule, as I already mentioned in relation to constructors.

The SqlMethod Attribute is required when you need to mark a method as deterministic or allow a method to be a mutator. There are four parameters for this attribute:

Deterministic This marks a method as deterministic (true) or nondeterministic (false). It is false by default.

OnNullCall If OnNullCall is false, the method returns NULL without evaluating the method when at least one of its input arguments is NULL. If OnNullCall is true (which is the default), the resulting value is determined by evaluating the method, regardless of the argument inputs.

IsMutator This property, which is false by default, dictates if a method can be a mutator of the instance. By default, method calls cannot modify the state of the instance. If you create a property that needs to modify instance state, you need to mark it as a mutator by using this property. Please note that a property is considered a mutator by default and does not require the use of this property.

DataAccess This property states whether a function or a method includes SQL SELECT statements. It can be set to either DataAccessKind.None or DataAccessKind.Read.

Let's review two examples of a CLR-based UDT implementation. The first is a simple Point class, shown in Figure 4. This type has two properties, X and Y, and a public method, DistanceTo, that returns the distance of the current Point to another Point that is passed in as an argument. You will see its usage in Yukon later in this article. Note that this could also easily be defined as a struct.

Figure 4 Point Class in Visual Basic .NET

Imports System Imports System.Data.SqlServer Imports System.Data.SqlTypes Imports System.Runtime.Serialization <Serializable(), SqlUserDefinedType(Format.Native)> Public Class Point Implements INullable Private is_Null As Boolean Private m_x As Decimal Private m_y As Decimal Public ReadOnly Property IsNull() As Boolean _ Implements INullable.IsNull Get Return (is_Null) End Get End Property Public Overrides Function ToString() As String If Me.IsNull Then Return "NULL" Else Return Me.m_x & ":" & Me.m_y End If End Function Public Shared Function Parse(ByVal s As SqlString) As Point If s.IsNull Then Return Nothing Else 'Parse input string here to separate out points Dim pt as new Point() Dim str as String = Convert.ToString(s) Dim xy() as String = str.Split(":") pt.x = xy(0) pt.y = xy(1) Return (pt) End If End Function Public Shared ReadOnly Property Null() As Point Get Dim pt As New Point pt.is_Null = True Return (pt) End Get End Property Public Property X() As Decimal Get Return (Me.m_x) End Get Set(ByVal Value As Decimal) m_x = Value End Set End Property Public Property Y() As Decimal Get Return (Me.m_y) End Get Set(ByVal Value As Decimal) m_y = Value End Set End Property Public Function DistanceTo (ByVal other AS Object) As Decimal Dim pt2 As New Point Dim pt1 As Point pt1 = me Return System.Convert.ToDecimal(System.Math.Sqrt( _ System.Convert.ToDouble( _ (pt1.x - pt2.x) * (pt1.x - pt2.x) + (pt1.y - pt2.y) * (pt1.y - pt2.y)))) End Function End Class

The second example is an Address class (see Figure 5), which stores address, city, state, and ZIP code data. This class demonstrates the UserDefined serialization format by implementing IBinarySerialize and the Write and Read methods.

Figure 5 Address Class in C#

using System; using System.Data.SqlTypes; using System.Data.SqlServer; using System.Data.Sql; [Serializable] [SqlUserDefinedType (Format.UserDefined, MaxByteSize = 150)] public class Address: INullable, IComparable, IBinarySerialize { private bool is_Null; private string m_address1; private string m_address2; private string m_address3; private string m_city; private string m_state; private string m_zip; #region INullable Members public bool IsNull { get {return (is_Null);} } public static Address Null { get { Address addr = new Address(); addr.is_Null = true; return (addr); } } #endregion #region Constructor public Address() { this.is_Null = true; this.m_address1 = ""; this.m_address2 = ""; this.m_address3 = ""; this.m_city = ""; this.m_state = ""; this.m_zip = ""; } #endregion #region String Conversion Members public override string ToString () { if (this.IsNull) return "null"; else { string delim = new string( (new char[] {';'})); return (this.m_address1 + delim + this.m_address2 + delim + this.m_address3 + delim + this.m_city + delim + this.m_state + delim + this.m_zip); } } public static Address Parse(SqlString s) { if(s.IsNull) return null; else { Address addr = null; string str = Convert.ToString(s); string[] a = null; a = str.Split(new char[] {';'} ); addr.m_address1 = a[0] == null ? "" : a[0]; addr.m_address2 = a[1]; addr.m_address3 = a[2]; addr.m_city = a[3]; addr.m_state = a[4]; addr.m_zip = a[5]; addr.is_Null = false; return (addr); } } #endregion #region Class Properties public string address1 { get {return (this.m_address1);} set { this.m_address1 = value; this.is_Null = false; } } public string address2 { get {return (this.m_address2);} set { this.m_address2 = value; this.is_Null = false; } } public string address3 { get {return (this.m_address3);} set { this.m_address3 = value; this.is_Null = false; } } public string city { get {return (this.m_city);} set { this.m_city = value; this.is_Null = false; } } public string state { get {return (this.m_state);} set { this.m_state = value; this.is_Null = false; } } public string zip { get {return (this.m_zip);} set { this.m_zip = value; this.is_Null = false; } } public string cityStateZip() { return this.m_city + ", " + this.m_state + " " + this.m_zip; } #endregion #region IComparable Members //Override the Equals method public override bool Equals (object other) { return this.CompareTo (other) == 0; } //Override the GetHashCode method public override int GetHashCode () { if (this.IsNull) return 0; return this.ToString ().GetHashCode (); } public int CompareTo (object other) { if (other == null) return 1; //by definition Address addr = other as Address; if (addr == null) throw new ArgumentException ("the argument to compare is not a Address"); if (this.IsNull) { if (addr.IsNull) return 0; return -1; } if (addr.IsNull) return 1; return this.ToString().CompareTo(addr.ToString()); } #endregion #region IBinarySerialize Members public void Write (System.IO.BinaryWriter w) { byte header = (byte)(this.IsNull ? 1 : 0); w.Write (header); if (header == 1) { return; } w.Write(this.address1); w.Write(this.address2); w.Write(this.address3); w.Write(this.city); w.Write(this.state); w.Write(this.zip); } public void Read (System.IO.BinaryReader r) { byte header = r.ReadByte(); if (header == 1) { this.is_Null = true; return; } this.is_Null = false; this.m_address1 = r.ReadString(); this.m_address2 = r.ReadString(); this.m_address3 = r.ReadString(); this.m_city = r.ReadString(); this.m_state = r.ReadString(); this.m_zip = r.ReadString(); } #endregion } //}

Using a UDT in the SQL Server Create Assembly

Once you have compiled this class into an assembly, the next step is to register it within SQL Server Yukon. To do this, you first need to load the assembly with the CREATE ASSEMBLY statement, which registers the assembly in the current database. Keep in mind that if you would like to use an assembly in multiple databases, each database must separately register the assembly. The syntax is as follows:

CREATE ASSEMBLY ASSEMBLY_NAME FROM 'PATH\ASSEMBLY_NAME.dll'

Note that the SQL assembly name must match the CLR assembly name, or you will not be able to register it. Here's an example:

CREATE Assembly YukonCLR FROM 'C:\Projects\"Yukon"\UDTs\Point\"Yukon"CLR.dll'

The registered assembly exists only in the context of the current database and is stored in several system objects. The implication here is that the DLL in the file system is only referenced once to create the internal representation of the DLL. After registering an assembly, its DLL can be deleted from the file system and your UDT in Yukon will continue to function normally. Since the assembly is loaded into the current database, it cannot be used by other databases. Each database that needs the assembly reference must make its own call to the CREATE ASSEMBLY statement. Although this may sound obvious, you should also note that only the compiled DLL is loaded into the database, whereas the source code is not. Like all objects in SQL Server, the name of the assembly needs to conform to sysname conventions (with a maximum length of 128 characters).

Create Type

Take a look at the syntax for the CREATE TYPE statement:

CREATE TYPE [TYPE_SCHEMA_NAME.]TYPE_NAME EXTERNAL NAME ASSEMBLY_NAME:CLASS_NAME

Once the assembly is loaded you use the CREATE TYPE statement to add the type to the list of available types for that database. Like an assembly, it will be created in the current database only, and also like an assembly it too is bound by sysname conventions. The following example will create the Point UDT from the Visual Basic® .NET CLR code I just showed:

CREATE TYPE Point EXTERNAL NAME YukonCLR:Point

The name of the UDT (in this case, Point) must be unique for the given schema name in the database context. This means that if you have two different assemblies registered in a database that have a common class name, only one of those classes can be created as a type in that database in the same schema. In the following code example, the Point class exists in both the YukonCLR and YukonCLR2 assemblies. Once the Point class of YukonCLR is created as a UDT in the current database, the Point class of YukonCLR2 cannot be created unless it is created in a different schema. For now, let's assume the following examples are created in the MySchema schema:

CREATE Assembly YukonCLR FROM 'C:\Projects\Yukon\UDTs\Point\YukonCLR.dll' CREATE Assembly YukonCLR2 FROM 'C:\Projects\Yukon\UDTs\Point\YukonCLR2.dll' CREATE TYPE Point EXTERNAL NAME YukonCLR:Point --OK CREATE TYPE Point EXTERNAL NAME YukonCLR2:Point --BAD - created in MySchema CREATE TYPE DifferentSchema.Point EXTERNAL NAME YukonCLR2:Point --OK - created in DifferentSchema CREATE TYPE Point EXTERNAL NAME YukonCLR2:Triangle --OK

Using a UDT

After the type is registered as one of the available types for the database, you can begin to use it in the creation of objects such as tables, as shown here:

CREATE TABLE Points ( PointID int NOT NULL, Pnt Point NOT NULL )

Using a UDT in a table definition requires no special coding. You define the table the same way you would if you were using one of the intrinsic data types, such as an int or nchar.

Before I go any further, let's take a look at the syntax for both property and method usage of a user-defined type in Yukon. It is very similar to C# and Visual Basic .NET syntax, except here the property or method is preceded by a double colon (::) instead of a period. By Beta2, the :: notation should disappear in favor of the period notation. The following shows this syntax for both a property and a method:

Property Use Syntax: <implemented_type>::<property_name> = <scalar_expression> Method Use Syntax: <implemented_type>::<method_name>([arguments])

In order to populate the table with data using T-SQL, you can run the following script:

DECLARE @startPoint Point DECLARE @endPoint Point SET @startPoint = CAST('10:10' AS Point) SET @endPoint::X = 5 SET @endPoint::Y = 3 INSERT Points VALUES(1, @startPoint) INSERT Points VALUES(2, @endPoint)

Much of this code should be familiar to anyone who has experience with T-SQL variables, DML statements, and SELECT statements from previous versions of SQL Server. There are, however, some elements of the T-SQL code that stand out. The first is the use of the CAST function to assign the X and Y values to @startPoint. When you use the CAST function in this manner, the Parse method is called on the UDT to populate the UDT instance with data. Next, the X and Y properties of the @endPoint variable are set individually, thus allowing you to explicitly pass values to properties of the UDT.

The next portion of the code inserts the instance of the UDT type into the Points table using the standard INSERT statement. Selecting the appropriate values from the UDT stored in the table is a process of specifying the property or method you want to call by using the syntax ColumnName::Property or ColumnName::Method. Even if the method takes no arguments, parentheses are required. In the following example, you will select the X and Y values from the entire set of points:

SELECT Pnt::X AS XValue, Pnt::Y AS YValue FROM Points

Here I will select a point and store it in the variable @pt, and then use it to check the distance to another point in the table using the DistanceTo method:

DECLARE @pt Point SELECT @pt = Pnt FROM Points WHERE PointID = 2 SELECT Pnt::DistanceTo(@pt) AS Distance FROM Points WHERE PointID = 1

The next example, Figure 6, shows how to use the Address class in Yukon (assuming it is part of the YukonCLR assembly and you have created its type already). First you create three variables of type Address. Next, you assign the address information and fetch the formatted city, state, and ZIP code using the cityStateZip method. The second part of the code makes a copy of the first address (@addr) into the second (@addr2) using the CAST function. This creates a copy, not a reference to the first variable. Any subsequent changes to @addr will not be reflected in @addr2. Finally, you check the variable @addr3 to see if it is null. Since it was only declared and never assigned any value, it will indeed be null, as you can see.

Figure 6 Using the Address Class

DECLARE @addr Address, @addr2 Address, @addr3 Address SET @addr::address1 = '1 Main Street' SET @addr::city = 'Dallas' SET @addr::state = 'TX' SET @addr::zip = '75321' PRINT @addr::cityStateZip() SET @addr2 = CAST(@addr AS Address) PRINT @addr2:: cityStateZip() IF @addr3 IS NULL PRINT 'YES' ELSE PRINT 'NO'

Dropping and Altering UDTs and Assemblies

UDTs are easily dropped by using the DROP TYPE statement, as seen in the following example, which drops the Point UDT:

DROP TYPE Point

But there is a catch—what if the UDT is currently in use (for example, in a column's definition) like in the Point example? Obviously, you cannot remove the type from the database if another object depends on it. So you must be sure to drop all objects that use the type before dropping the type itself. The same holds true for dropping assemblies. All UDTs must be dropped before you can drop an assembly. Based on this latest example, the following would have to be done to remove my YukonCLR assembly:

DROP TABLE Points DROP TYPE POINT DROP TYPE Address DROP ASSEMBLY YukonCLR

As you can see, the syntax for dropping any of these objects is simple. When dropping an assembly, you remove the code base from your current database and must have a compiled version available if you want to add it back to your database.

ALTER TYPE is not valid to use due to a variety of factors such as storage representation, ordering, and interfaces. You will have to drop and recreate these objects if a change needs to be made. ALTER ASSEMBLY, however, is valid for making bug fixes to existing assembly code.

In case you're wondering, there is deep support for UDTs both in the managed ADO.NET stack and in the unmanaged client APIs—OLE DB, ODBC, and ADO.

Conclusion

You now know how to implement user-defined types in Yukon and .NET and how to use these implementations in Yukon. Of course, there are other features of UDTs that I did not explore here that will likely be covered in future issues of MSDN® Magazine.

For related articles see:
Creating User-Defined Data Types

Peter W. DeBetta is a trainer at Wintellect, a consultant, and developer who helps clients develop enterprise-level software solutions using Visual Basic, ASP, C#, ASP.NET, and SQL Server. Peter has coauthored several books and is currently working on a book on SQL Server "Yukon" to be published by Microsoft Press.