At some point of time, we as developers feel the need that the standard data types provided by an application is not sufficient to model our application. For example, if an application supports data types like integer, decimal etc, sometimes we might need to create a different data type to hold some complex piece of information. In the development languages world like C#, VB.NET etc, new data types are created by modelling them as classes or structures. For example, you can create a data type to hold geospatial information for your application and then use that data type as though it was a regular data type (through features like operator overloading etc). Although this is great from a programming languages point of view, what about the case when you want to do the same in a database (especially SQL Server)? Can we create new data types in the database?

SQL Server 2000 had very limited support for extending the type system. I would not call it as "extending" the type system in SQL Server 2000, since the feature available basically allowed you to only "alias" an existing data type as a new data type with some associated rules. To understand what SQL Server 2000 supported, consider the case wherein we want to create a data type to hold the gender values for a person. The rules for this data type are pretty simple. The gender needs to hold three values (Male, Female, Undefined) and has to have a width of 1 character. Here is what you would do in SQL Server 2000 to model this:

CREATE RULE GenderRule AS
	@value IN ('M', 'F', 'U')
GO
sp_addtype 'Gender', 'CHAR(1)', 'NOT NULL'
GO
sp_bindrule 'GenderRule', 'Gender'
GO

We start off by creating a rule called GenderRule that allows only permissible values. We then create a new user-defined alias type called Gender and then bind the rule to the type using the sp_bindrule command. Once this is done, we can use this data type in regular T-SQL expressions as follows:

CREATE TABLE People
(
	Identification	INT,
	Name		VARCHAR(50),
	Sex		Gender
)
GO

Note that we can use the type that we created as though it was a regular data type in CREATE statements, procedure parameters, variable declaration etc. Once used, we can insert only the permissible values into that particular column. Why did SQL Server 2000 have this capability? One simple reason is to allow people to create standard business terms as data types and then enforce standardization across the entire application. Say for example, we did not have a data type called Gender, then across the system, people could have create either a CHAR or a VARCHAR type and associated possibly different rules to the same. Rather, by creating the user-defined type, we can have the entire application use this type across the board and ensure standardization.

Although this feature is really useful, it still has limitations. You can only create alias types for existing SQL Server types. You cannot extend the type system of SQL Server. For example, you cannot create a type to hold latitudes and longitudes. This is where SQL Server 2005 steps in and allows you to truly extend the type of the database. It makes this possible through the CLR integration capabilities. In this article, we will discuss on how we can create a new user-defined type in SQL Server 2005 and then use it as a normal data type.

Implementing a User Defined Type

Implementing a user defined type is quite simple in SQL Server 2005. The following are the high-level steps that you need to follow:

1. Create and build an assembly that defines the UDT. UDT assemblies follow a certain structure and it is quite easy to author them. Visual Studio 2005 provides a lot of help to you when writing the code.
2. Register the assembly. Once the assembly has been developed, you can deploy it either through the Visual Studio auto deploy feature or by using the CREATE ASSEMBLY command.
3. Create the UDT. Once the assembly has been registered, you can then use the CREATE TYPE statement to create a new type linked to the assembly. Once done, the type has no dependencies on the external files that have been authored and lives entirely in the database in which it was created. Note that if you used Visual Studio auto deploy feature, the step of creating the UDT is also done automatically for you.
4. Start using the UDT.

To illustrate each of the points above, in this article, we will build a user-defined type called ComplexNumber. All of us, during our earlier mathematics days, would have heard about complex numbers. A complex number has a real part and an imaginary part and is of the syntax a + bi or a - bi. Once you have a complex number, you can then add and substract another complex number from it. If you need more information on complex numbers, you can refer to this link: Complex Numbers. Note that as part of this example, we will implement only addition and subtraction of complex numbers.

Creating the assembly for the UDT

As the first step, let us create the assembly for the UDT. For this, you need to create a new C# Database project in Visual Studio 2005. Name the project as ComplexNumber and also choose the database in which you wish to deploy the UDT. Once you have created the project, right click the project and choose Add > User Defined Type. Name the user defined type as ComplexNumber. Once you have created the type, you can see that Visual Studio fills out a major portion of the code with template information that you then need to complete. You will also notice that your class implements INullable. This is required to support the concept of nullability. UDTs loaded into SQL Server and aware of NULLs, but for the UDT itself to recognize a null value, we need to implement this interface.

First, let us define the namespaces that we will need for our complex number project. Apart from the list of namespaces that are already added, we need a few more. Here is the full list that we will need.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Text.RegularExpressions;

Next, notice that an attribute has been added above your class. The attribute definition that we need should look like the following:

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native, IsByteOrdered=true)]

The [Serializable] attribute is optional, but you will need the SqlUserDefinedType attribute. This attribute basically specifies the storage options for the UDT. For our example, we need two settings:

1. The Format.Native indicates that we are using the native serialization that is automatically provided for UDTs to persist them onto disk. If you need, you can also implement your own serialization by changing the value of this attribute.
2. The IsByteOrdered=true indicates that the UDTs bits are laid out in sequence and the results of comparision are as same as what would have happened in managed code.

Next, let us declare a few variables to be used within the program.

// Declare the components of the complex number
public int realPart, imaginaryPart;
private bool m_Null;

To implement nullability behavior, you need to create a property called IsNull. This property is used by SQL Server to determine if the UDT is indeed NULL. If so, then the default behavior of NULLs that SQL Server implements will take over. This means that, for example, you can use the IS NULL construct to check if the UDT is null. Apart from the IsNull property, you should also implement a static property called Null that returns a null instance of the UDT. This allows the UDT to return a null instance if the value is indeed NULL in the database. Here is the source for these two properties.

// This property just returns the null indicator
public bool IsNull
{
	get
	{
		// Put your code here
		return m_Null;
	}
}

// This property creates a null instance
public static ComplexNumber Null
{
	get
	{
		ComplexNumber h = new ComplexNumber();
		h.m_Null = true;
		return h;
	}
}

The next important method to implement is the Parse method. The Parse method is called to convert a string representation into a UDT and must be a static method. Here is the source for the same.

// This method is called when a string value is to be converted into
// the UDT. Here, we split the string based on the format of the value.
// The method is not called if a null is assigned as indicated by the
// attribute
[SqlMethod (OnNullCall=false)]
public static ComplexNumber Parse(SqlString s)
{
	if (s.IsNull)
		return Null;

	// Define a regular expression pattern to match the structure of a complex
	// number
	ComplexNumber u = new ComplexNumber();
	Regex r = new Regex(@"([+-]?\d+)([+-]\d+)i", RegexOptions.IgnoreCase);

	// Apply the pattern and if there is a match, we extract
	// the components of the complex number
	try
	{
		Match m = r.Match(s.Value);
		if (m.Groups.Count != 0 || m.Groups.Count != 2)
		{
			u.realPart = Int32.Parse(m.Groups[1].Value);
			u.imaginaryPart = Int32.Parse(m.Groups[2].Value);
		}
	}
	catch
	{
		throw new Exception(String.Format ("Input {0} does not confirm to the required
			complex number standard. Input standard is a+bi.", s.Value));
	}
		
	return u;
}

Some points to note. Note that in the Parse method, we first check to see if the string is null. If so, we just return back. Now, based on our definition of the structure of a complex number, we create a regular expression pattern and then match the incoming string to that pattern. If the pattern matches, we extract the real part and imaginary part, otherwise, we throw an exception back to the user. Note that in our implementation of a complex number, the format is a+bi without any spaces inbetween. We have also added an attribute on top of this method that prevents this method being called with a null instance of the UDT.

Ok, we now have a method to convert strings to complex numbers, but what about the other way around? For example, what if we need a string representation of our complex number? For this, we need to implement the ToString method which just does the opposite of the Parse method. Here is the source for the same.

// This method is called when we need a string representation
// of the UDT
public override string ToString()
{
	StringBuilder b = new  StringBuilder(10);
	b.Append(this.realPart);

	// If the imaginary part is positive, we need the positive symbol.
	// Negative numbers always have the symbol in place
	if (this.imaginaryPart > = 0)
		b.Append ("+");
	b.Append(this.imaginaryPart);
	b.Append("i");

	// Return the formatted value
	return b.ToString();
}

The implementation is pretty simple. We just use a string builder object to build a string representation of our complex number and then return it back. We can also expose the components of the complex number as a set of properties as shown.

// A property that returns the real part of a complex number
public Int32 RealPart
{
	get { return this.realPart; }
	set { this.realPart = value; }
}

// A property that returns the imaginary part of a complex number
public Int32 ImaginaryPart
{
	get { return this.imaginaryPart; }
	set { this.imaginaryPart = value; }
}

These two properties will allow anyone using our complex number to set the components of the complex number independently. Actually, this is it! Our UDT is fully functional now, but for the sake of completeness, we will implement two methods that will allow users to add and subtract complex numbers. Here is the source for the two methods.

// Implement a method that allows the addition of two complex
// numbers. The result is another complex number
public ComplexNumber AddTo (ComplexNumber c2)
{
	ComplexNumber result = new ComplexNumber();
		
	result.realPart = this.realPart + c2.realPart;
	result.imaginaryPart = this.imaginaryPart + c2.imaginaryPart;
		
	return (result);
}
	
// Implement a method that allows the subtraction of two complex
// numbers. The result is another complex number
public ComplexNumber SubtractFrom (ComplexNumber c2)
{
	ComplexNumber result = new ComplexNumber();
		
	result.realPart = this.realPart - c2.realPart;
	result.imaginaryPart = this.imaginaryPart - c2.imaginaryPart;
		
	return (result);
}

Ok. Now that we are completed in terms of coding for the user-defined type, we can start deploying the UDT in the database.

Registering and Creating the UDT 

To register the assembly, we will need to use the CREATE ASSEMBLY command and to create the type we need to use the CREATE TYPE command. Here are the commands for the same. Note that in the example below, I've given the path to the DLL in multiple lines, but before you execute it, make sure that it is on the same line . I did it to avoid horizontal scrolling of the article.

CREATE ASSEMBLY ComplexNumber
	FROM 'C:\Documents and Settings\srinivass\My Documents\
			Visual Studio 2005\Projects\SQLCLR\
				ComplexNumber\bin\Debug\ComplexNumber.dll'
	WITH PERMISSION_SET = SAFE
GO
CREATE TYPE ComplexNumber
	EXTERNAL NAME ComplexNumber.ComplexNumber
GO

Once the above commands are successful, we are ready to use the complex number UDT. Note that if you use Visual Studio's auto deployment feature, the above commands are automatically done for you. To do this, right-click the project and choose Deploy. This will deploy the UDT onto the database connection that you gave when you created the project.

DECLARE @c1 ComplexNumber
SET @c1 = CONVERT (ComplexNumber, '3+4i') -- Parse method is invoked
SELECT @c1.RealPart, @c1.ImaginaryPart, @c1.ToString()

In the above example, we create a variable of our UDT and then play around with the various methods that we created. Fun, isn'nt it? Here is another example that works with the other methods that we created.

DECLARE @c1 ComplexNumber
DECLARE @c2 ComplexNumber
SET @c1 = CONVERT (ComplexNumber, '3+4i')
SET @c2 = CONVERT (ComplexNumber, '4+7i')
SELECT (@c1.AddTo (@c2)).ToString()

If you want to simulate the exception that we raised in the code, you can try the following example.

DECLARE @c1 ComplexNumber
BEGIN TRY
	-- Note the space in the number. This will raise
	-- the error
	SET @c1 = CONVERT (ComplexNumber, '3+ 4i')
	SELECT @c1.ToString()
END TRY
BEGIN CATCH
	SELECT error_number(), error_message()
END CATCH

Till now, we have seen how to define and consume user defined data types in the database. Now, let us see how to consume them in the client application tier using ADO.NET.

Consuming User Defined Types in ADO.NET

Consuming user-defined types on the client side is quite easy in ADO.NET. You can both consume UDTs from the database and also send them as parameters to the database. ADO.NET provides a new parameter type called Udt that allows you to pass-in UDT values. To read UDT values, you will need to type-cast the values from the database into the UDT type and then use the same. Alternatively, you can also use the binary stream as it is from the database. To do all of these, the only requirement for using UDTs in the client application tier is that the client application must have access to the assembly that implements the UDT. To provide access, you can just make a reference from your client application to the UDT assembly. To illustrate this concept, let us create a simple command-line application that inserts 10 complex numbers into a table and then reads them back. Here is the source for the same:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace TestComplexNumber
{
	class Program
	{
		static void Main(string[] args)
		{
			string sqlText;
			int retVal;
			
			using (SqlConnection oConn = new SqlConnection(
				"Data Source=name;Initial Catalog=AdventureWorks;Integrated Security=SSPI"
				))
			{
				SqlParameter oParam;
				SqlCommand oCmd = new SqlCommand();
				oCmd.CommandType = CommandType.Text;
				oCmd.Connection = oConn;

				sqlText = "CREATE TABLE ComplexNumbers (colA ComplexNumber)";
				try
				{
					// Open a connection and create our test table
					oConn.Open();
					oCmd.CommandText = sqlText;
					retVal = oCmd.ExecuteNonQuery();
					
					//Prepare for inserting 10 complex numbers into our table
					sqlText =  "INSERT INTO ComplexNumbers VALUES (@value)";
					oCmd.CommandText = sqlText;
					
					// Specify the parameter as a UDT and provide the name of the
					// UDT that was registered in the database
					oParam = new SqlParameter("@value", SqlDbType.Udt);
					oParam.UdtTypeName = "dbo.ComplexNumber";
					oCmd.Parameters.Add (oParam);
					
					// Insert 10 numbers into the table
					for (int i = 0;  i <  10; i++)
					{
						// Create a new complex number instance
						ComplexNumber o = new ComplexNumber();
						o.realPart = i;
						o.imaginaryPart = i+1;
					
						Console.WriteLine("Inserting {0}", o.ToString());

						// Assign the parameter value to the command and execute it
						oParam.Value = o;
						retVal = oCmd.ExecuteNonQuery();
					}
					Console.WriteLine();
					
					// Read back the 10 inserted numbers
					oCmd.Parameters.Clear();

					// Setup the command to read from the table
					sqlText = "SELECT colA FROM ComplexNumbers";
					oCmd.CommandText = sqlText;
					
					// Create a reader and then iterate over the same
					SqlDataReader rdr = oCmd.ExecuteReader();
					while (rdr.Read())
					{
						// Type cast the value and print the same
						Console.WriteLine("Reading {0}",
							((ComplexNumber) rdr[0]).ToString());
					}

					Console.WriteLine();
					Console.WriteLine ("Completed. Press any key to exit...", retVal);
					Console.Read();
				}
				catch (Exception e)
				{
					Console.WriteLine (e.Message);
				}
			}
		}
	}
}

You can see that the program is broken into 3 sections:

1. In the first section, we just create a table called ComplexNumbers with a single column called colA.
2. In the next section, we create an INSERT statement into the table and then associate a parameter with that statement. Note that we have used a parameter type of UDT and then specify the database UDT name to use in the UdtTypeName property. What we then do is create a new instance of our UDT in a loop and then assign it to the parameter.
3. In the last section, we setup the command to read from the database table and then use a reader to iterate over the results. Note that UDTs will be streamed as binary from the database and will need to be converted into the type of UDT before being used. This is what we do in the reader code.

Once you execute the above program, you should see the following output.

Ok, that brings me to the end of this article on how to create and use the new user-defined type (UDT) to build some really interesting applications. The UDT feature allows you to truly extend the type system of SQL Server to handle some intricate and complex data types. In this article, we have used the native serialization mechanisms for UDTs which should suffice for most cases. However, sometimes, you may want to implement your own serialization. This will be an example that I will post onto my resources section. You can also refer to the SQL Server books online for more examples. Do send me your feedback and comments to srisamp@gmail.com.