WEBINAR:
On-Demand
Application Security Testing: An Integral Part of DevOps
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.