Browse DevX
Sign up for e-mail newsletters from DevX


Creating User-Defined Data Types in Yukon : Page 3

One key feature of CLR support is the ability to create User Defined Data Types (UDT). This article demonstrates how to create your own UDT using Yukon and the Whidbey-release of Visual Studio .NET.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Registering the Class
After you have created and compiled the class into a DLL, you need to go to SQL Server and perform two tasks.

  • Register the assembly with SQL Server.
  • Register the type with SQL Server.
The following SQL code demonstrates how to perform these tasks:


Using the Class
Once you have registered the class, you can begin using that class as a data type in a table definition. When you have created a table supporting your data type, you can begin adding data to your table. The following code snippet shows how to create a table using your custom data type, how to insert data into your table, and how to query data from a column defined as your custom data type.

-- Create table with data type CREATE TABLE SSNTEST (ssntestid int primary key not null, cssn DpsiSSN) GO -- Insert some data DECLARE @cParam DpsiSSN SET @cParam = CONVERT(DpsiSSN,'123456789') INSERT INTO ssntest (cssn) VALUES (@cParam) SELECT cSSN::MySSN FROM ssntest WHERE Right(cSSN::MySSN,4) = '0000' SELECT cSSN::MySSN FROM ssntest ORDER BY cSSN::MySSN

There are a couple of items to note from the above example. The first item is the use of the Cast() statement. In order to insert data into a custom data type, you need to cast the SQL Server data types into your own data types. The next item to note is the syntax for querying data from a custom data type. The syntax for querying data from a custom data type is <ColumnName>::<PropertyName>. From my tests, I was able to sort, group and query by attributes contained in my custom data type.

As you can see, creating custom data types in SQL Server Yukon is pretty simple stuff. Finally, you can create your own "real" data types with validation rules and all. I hope you can see now how to take advantage of this feature with minimal effort.

Rod Paddock is the editor of CoDe Magazine. Rod has been a software developer for more than 10 years and has worked with tools like Visual Studio .NET SQL Server, Visual Basic, Visual FoxPro, Delphi and numerous others. Rod is president of Dash Point Software, Inc. Dash Point is an award winning software development firm that specializes in developing applications for small to large businesses. Dash Point has delivered applications for numerous corporations like: Six Flags, First Premier Bank, Intel, Microsoft and the US Coast Guard. Rod is also VP of Development for SQL Server tools maker, Red Matrix Technologies. You can contact Rod here.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date