Creating User-Defined Data Types in Yukon : Page 3

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:

   CREATE ASSEMBLY SSNLIbrary
   FROM 'c:\test\SSNLibrary.dll'
   CREATE TYPE [DpsiSSN] 
   EXTERNAL 
   NAME [SSNLibrary]:[SSNLibrary.DpsiSSN]

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.

Previous Page