Professional SQL Server™ 2005 Programming

14.9. Custom Data Types

Sometimes you have the need to store data that you want to be strongly typed, but that SQL Server doesn't fit within SQL Server's simple data type list. Indeed, you may need to invoke a complex set of rules in order to determine whether the data properly meets the type requirement or not.

Requests for support of complex data types have been around a very long time. Indeed, I can recall being at the Sphinx Beta 2.0 — known to most as Beta 2 for SQL Server 7.0 — event in 1998, and having that come up as something like the second most requested item in a request session I was at. Well, it took a lot of years, but it's finally here.

By utilizing a .NET assembly, we can achieve a virtually limitless number of possibilities in our data types. The type can have complex rules or even contain multiple properties.

Before we get to the syntax for adding assemblies, let's get an assembly constructed.

NOTE

The sample used here will be the ComplexNumber.sln solution included in the SQL Server samples. You will need to locate the base directory for the solution — the location of which will vary depending on your particular installation.

We need to start by creating the signature keys for this project. To do this, I recommend starting with your solution directory being current and then calling sn.exe using a fully qualified path (or, if your .NET framework directory is already in your PATH, then it's that much easier!). For me, it looks like this:

C:\Program Files\Microsoft.NET\SDK\v2.0 ...