What do you do, when you want a data type that should represent more than one

interrelated values? Would you do this with two separate data types? Not a good

idea! Is it possible that you can do this with just one single data type? Yes,

you can. We have been provided a “proper” way to sort this out: The

User-Defined Type in SQL Server 2005. Well, you had this in SQL Server 2000,

didn’t you? Yes, but in this case it is really enhanced, or in other words, it

is greatly empowered.

The integration of the .NET CLR (Common Language Runtime) into SQL Server 2005

is the man of many parts, that allows us to create our own data type by using

our favorite manage code, like C#.NET. This article speaks about how to create

a user-defined type and how can it be useful in our day-to-day operations. This

article does not contain much detail about user-defined types, but the

manipulation of them. The reason that made me write this kind of article is,

being a developer, I always love to have hands-on-experience and the concept of

this can be found in many forms.

What is the UDT that we are going to create?

As I really wanted to put this in a more practical manner, I went through some

of my applications that were done using SQL Server 2000 in order to find a

proper kind of value to make a UDT. Finally, I found one in one of the

Sales-Automation applications of mine. One of the processes that is involved in

this operation is to have a count on both physical and system stock on a daily

basis of items. The physical stock and system stock are stored separately in

two different columns. Why do we not have a single column for this purpose?

This thought came up as soon as I saw my own design. So, this little scenario

is going to be used for our first UDT, though a little simple.

Naming the new UDT as “ItemStock” is fine I think. The “ItemStock” will have

two parts: Physical Stock and System Stock. Having a third part, something like

Unit-Of-Measure will give our UDT more flexibility, but I will not go ahead

with it, just to keep things simple. Remember the KISS principle?

What is the first step?

The first step of the procedure is to have an assembly that contains the

implementation of the UDT. Since my favorite CLR-supported language is C#.NET,

we’ll create the assembly with C#.NET. All you have to do is, open Visual

Studio 2005 and create a new project of the type “Database”. Once created, add

a new item which should be a “User-Defined Type”. This leads to the creation of

a skeleton of the type we need. Here is the cleaned-up code:

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct ItemStock : INullable   
{
   public override string ToString()
   {
      return "";
   }
   public bool IsNull
   {
      get
      {
         return m_Null;
      }
   }
   public static ItemStock  Null
   {
      get
      {
         ItemStock
      
          h = new ItemStock ();
         h.m_Null = true;
         return h;
      }
   }
   public static ItemStock Parse(SqlString   s)
   {
      if (s.IsNull)
         return Null;
      ItemStock 
       u = new ItemStock ();
      return u;
   }
}

I’ll give you simple description on some important parts of this code snippet.


• [Serializable]

This allows the UDT to be serialized.

• [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]

This is a required attribute for UDTs. The required 
property “Format” determines the serialization format. With the version of Beta 
2 April CTP, there are three elements under the Format enumerator: Native, 
Unknown and UserDefined. This attribute can be specified with some more 
named-parameters like IsByteOrdered, IsFixedLength, MaxByteSize, Name and 
ValidationMethodName.

• Format.Native

This is the native SQL Server binary serialization 
which is the fastest. This can only be applied to fixed-length, value-type data 
types. The named-parameter “MaxByteSize” cannot be specified with this type.

• Format.UserDefined

This is basically used for reference types. If this is 
specified, the serialization have to be implemented by ourselves. This can be 
done by implementing IBinarySerialize interface.

• INullable

Implementing INullable interface makes the UDT null 
aware. The public property “IsNull” of the INullable must be implemented too.

• Static Null method

This method must be implemented that returns the type 
of UDT itself.

• Static Parse method

This static method makes UDT convert-possible from a 
string value.


• ToString()

This Overrides the UDT’s virtual ToString() method. The 
convention of the UDT back to the string is done through this.

Well, I think that I have covered some basic, important areas of the UDT. Now,

have a look on our UDT: The ItemStock.

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct ItemStock : INullable
{
//these three variables hold the value of physical stock, system stock, 
//and the indicator whether the value of UDT is null respectively.
private int _physicalStock;
private int _systemStock;
private bool _isNull;
//The INullable readonly property.
public bool IsNull { get { return _isNull; } }
//This method returns null-valued UDT.
public static ItemStock Null
{
get
{
ItemStock itemStock = new ItemStock();
itemStock._isNull = true;
return itemStock;
}
}
//Coversion from string to UDT.
//If the value is null, return null-valued UDT.
//If not, validate and create a new ItemStock UDT.
public static ItemStock Parse(SqlString value)
{
if (value.IsNull)
return ItemStock.Null;
ItemStock itemStock = new ItemStock();
try
{
string[] values = value.ToString().Split('|');
itemStock._physicalStock = Convert.ToInt32(values[0]);
itemStock._systemStock = Convert.ToInt32(values[1]);
itemStock._isNull = false;

return itemStock;
}
catch (Exception exception)
{
throw exception;
}
}
//String representation of the UDT.
public override string ToString()
{
if (this.IsNull)
return "NULL";
else
return this._physicalStock + " | " + this._systemStock;
}
//Returns the physical stock. This is a read-only property
public int PhysicalStock { get { return this._physicalStock; } }
//Returns the system stock. This is a read-only property
public int SystemStock { get { return this._systemStock; } }
//Returns the difference between physical and system stock. This is a read-only property
public int Difference { get { return _physicalStock - _physicalStock;}}
}

Well, the code is very simple. You can clearly see, we have implemented all

what we discussed so far. In addition to that, three new properties have been

implemented. You will see the usability of it soon.

The Second Step

Now it is time to register the assembly we created in the SQL Server 2005. Open

the MS SQL Server Management Studio and Click on “New Query”. Set the database

you want to add the UDT and place the code below on it.

CREATE ASSEMBLY SQLServerUDTs
FROM '{FilePath}\SQLServerUDTs.dll'

Here is the Last step

Once you run the second step, the last step of the procedure is, create a data

type base on the UDT we created. See the code below.

CREATE TYPE ItemStock EXTERNAL NAME SQLServerUDTs.ItemStock

The above code creates a UDT named “ItemStock” that points to the assembly

registered. The “SQLServerUDTs.ItemStock” represents the namespace and the UDT

name in the assembly.

Time to use it

We have done! To see the usability of the newly created UDT, let’s create a

simple table that contains the UDT and insert some records.

CREATE TABLE Items
(Id int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100) NOT NULL,
InStock ItemStock NULL)
INSERT INTO Items ([Name], InStock) VALUES ('Dining Table 1', '0|0')
INSERT INTO Items ([Name], InStock) VALUES ('Dining Table 2', NULL)
INSERT INTO Items ([Name], InStock) VALUES ('Dining Table 3', NULL)
INSERT INTO Items ([Name], InStock) VALUES ('Dining Table 4', NULL)
--this will cause to an exception
INSERT INTO Items ([Name], InStock) VALUES ('Dining Table 4', 'p|s')

Ok, now table is ready with data. Let’s see how we it help us in day-to-day

operations.

-- Select all columns from the tables.
-- See the way we have to access UDT properties
SELECT Id, [Name], InStock.ToString(), 
InStock.PhysicalStock, InStock.SystemStock
FROM Items
-- Updating records
UPDATE Items
SET InStock = '1860|1860'
WHERE Id = 3
UPDATE Items
SET InStock = '138|156'
WHERE Id = 4
-- applying for conditions
SELECT Id, InStock.Difference
FROM Items
WHERE InStock.PhysicalStock < InStock.SystemStock

Well, you can see now how the new stuff that comes with SQL Server 2005, make

our life easier. Especially the MS .Net Framework integration, which is the

most intriguing part for me. You can see some of the experiments I have done

with this and more are being done.

As I have stated above, this article started you off on some basic UDT stuff.

Well, in my next article, I will be covering more on this and I will highly

appreciate if you can give some comments on this. I know definitely, it will

help me out with next part on UDT.