Advertisment

Create datatypes for SQL Server 

author-image
PCQ Bureau
New Update

We've been exploring the usefulness of one of the best features of the upcoming version of SQL Server 2005 codenamed 'Yukon'. We've seen how you can create UDIs (User Defined Functions), Stored Procedures and Aggregates, using your favorite .NET language within the SQL Server database engine itself. This month we conclude this mini-series with a look at creating and using your own custom data types for SQL Server 2005 using .NET.

Advertisment

Custom datatypes are useful when you want to store certain information as atomic objects instead of as individual fields. For instance, in an Employee database, the Address fields are usually stored as Address 1, Address 2, City, State, PIN and Country. This is because the database has no datatype object that can store all these fields together logically, since the datatypes in databases are always primitive types like numbers, dates and strings. 

To get around this limitation you can create UDTs (User Defined Types). This has always been possible, though, difficult to perform in SQL Server. With .NET 2.0 now built into the engine, this has now become almost trivial. Let's see what you need to do to get this working.

Direct Hit!
Applies to: Developers, DBAs
USP: Create complex datatypes to store multiple fields logically
Links:

http://msdn.microsoft.com 
Advertisment

Open up VS.NET 2005 and create a new SQL Server Project. Now right click on the project and select Add>User-Defined Type. Give the new name as ContactInfo and press OK when prompted. VS creates a new template UDT with the given name and some properties already created for you. However, we will add a set of new properties to store certain contact information such as Address, City, Mobile and E-mail in it. The code below shows the complete set. This defines a new .NET class with the properties that we want in our datatype. Remember you can even add methods into the class if you wish.

Imports System



Imports System.Data.Sql


Imports System.Data.SqlTypes





_


_


Public Class ContactInfo


Implements INullable





The new type appears in the Data Type dropdown when adding a new field

Advertisment

Private m_isNull As Boolean



Private m_Address1 As String


Private m_Address2 As String


Private m_City As String


Private m_Mobile As String


Private m_Email As String





...





You can query on the individual properties of the type you created in SQL easily

Public Property Mobile() As String



Get


Return m_Mobile


End Get


Set(ByVal value As String)


m_Mobile = value


End Set


End Property





Public Property Email() As String


Get


Return m_Email


End Get


Set(ByVal value As String)


m_Email = value


End Set


End Property





End Class
















Advertisment

Now compile the project to get the binary .NET DLL. Open up the SQL Server 2005 Management Studio, connect to the server you want and then open the database into which you wish to add the new datatype. Open a query window and enter the following.

CREATE TYPE ContactInfo EXTERNAL NAME SQLCLR.

When you execute the statement, the new datatype gets registered into the SQL Server database engine. You can now use this type as you would any other type. 



For instance, let's create a new table in both graphical and script mode. In the database you added the type, open the Tables folder and right click to select New Table. In this add a new column and click on the Data Type dropdown. At the end of the list you will see the datatype you just added. To add it using SQL statements, create a table using the following.

Advertisment

CREATE TABLE Contacts



(


ID INT NOT NULL,


sName VARCHAR(75) NOT NULL,


ciContact ContactInfo,


sCompany VARCHAR(50) NOT NULL


)




Now insert some values into the new table (and especially the field using the new datatype) like this.

DECLARE @ci ContactInfo



SET @ci.Address1 = 'MyAddr1'


SET @ci.Address2 = 'MyAddr2'


SET @ci.City = 'Gurgaon'


SET @ci.Mobile = '9810098100'


SET @ci.Email = 'vinod@enterpriseinfotech.com'



Advertisment

You can also query from the table and the field like this.

SELECT 



sName, 


ciContact.Address1, 


ciContact.Address2, 


ciContact.City, 


ciContact.Mobile, 


ciContact.Email 


FROM 


Contacts






As you can see, adding a new datatype that logically groups information is quite easy using .NET. Since the type now works within the SQL Server itself, it acts like any ordinary type and you can continue to use all the SQL functionality you are familiar with on this type as well.

We will cover some new features and XML integration in SQL Server 2005 in the coming issues.

Vinod Unny



Enterprise InfoTech

Advertisment