Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

User Defined Data Types in SQL Server 2005

By Dinesh Priyankara, (first published: 2005/08/02)

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.
Total article views: 35164 | Views in the last 30 days: 26
 
Related Articles
ARTICLE

A Stock Price Correlation Matrix

Building a Stock Price Correlation Matrix Using TVPs, a CROSS JOIN and the PIVOT Function

ARTICLE

Calculating Stock Returns Using the ROW_NUMBER function and CTEs

In finance, when pricing data isn't available on last day of week, month or year, calculating return...

FORUM

Multiple parents tree (or digraph) implementation

How to implement a query to return the whole graph

FORUM

column to calculate the balance stock dynamically

column to calculate the balance stock dynamically

FORUM

'As Of Date' Stock -

Help need to find out the As of Date stock

Tags
sql clr    
sql server 2005    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones