SQL Server 2000 has a scalar data-type system that defines data types such as int or float, which DBAs commonly think of as "primitives" because they contain values that you can't break down further into components. However, SQL Server 2000 doesn't provide a practical way to extend this type system. When you extend a type, you add new types to it that let you work with more complex values. You might wonder why you'd want to extend a data type such as int, and in SQL Server 2000, you wouldn't. Instead of extending data types, you use SQL Server 2000's built-in constraints. However, a custom data type based on multiple fields—similar to SQL Server's built-in datetime multifield data type—might be useful. SQL Server 2005 (formerly code-named Yukon) lets you use its new user-defined data type (UDT) capability to create your own multifield scalar data types and treat them the same way you do built-in scalar data types.
For example, imagine you have a table column that holds a scalar data type. Given a sample set of data that contains the triples 12 4 9, 13 5 2, and 9 14 11, you might recognize each triple as an ordered collection of items known as a vector and decide that each triple needs three columns. You'd know from the experience of micro-parsing a field and from the rules of first normal form that the space you'd save squeezing each triple into one column would be a false economy.
However, not all triples are vectors. Consider this set of data: 12 1 1998, 3 5 1992, 1 4 1996. Writing the numbers in the format
12/1/1998, 3/5/1992, 1/4/1996
makes it clear that they're dates. Again, from experience, you know that storing a date in three columns would make it difficult to work with. In this case, the triple should use one column because storing a date in three columns makes no sense. You store a date in one column because even though it has three fields, it's a scalar value, and the SQL Server scalar data-type system provides the datetime data type to store it.
Other multifield values can be scalars, too. Angles such as latitude and longitude often contain four fields: degrees, minutes, seconds, and direction. These values look like 34°6'12"N or 61°35'19"W. Unfortunately, the SQL Server scalar type system doesn't have latitude and longitude data types.
SQL Server 2005 lets you create new scalar types by using any programming language that the .NET Common Language Runtime (CLR) supports. You can add Latitude and Longitude scalar data types to SQL Server and store instances of each in its own column, just as you store a date in its own column with the datetime data type in SQL Server 2000. Let's look at how you can create a UDT for latitude in SQL Server 2005 by using C#. Note that the sample code that accompanies this article uses the beta release of SQL Server 2005 that Microsoft distributed at the Professional Developers Conference (PDC) in October 2003. You can download the Latitude.cs file, which contains complete sample code for this article's example, at http://www.sqlmag.com, InstantDoc ID 42169.
Goals for the Latitude Data Type
Before you design a UDT, you need to decide what characteristics you want it to have. First, in this example, you want the UDT to store latitude to the nearest second of arc without any loss of data. Second, you want to be able to use in SQL expressions a string representation such as 34°6'12"N that has the fields degrees, minutes, seconds, and direction. Each field has a restricted range of values. Degrees are in the range 0 to 90, and minutes and seconds are in the range 0 to 59. The direction must be N or S. Third, you must be able to use Latitude values in magnitude comparisons and ORDER BY clauses.
Listing 1 shows a simple SQL batch that creates an environment for using the Latitude data type. Listing 1's code creates the Places table, in which each row has a place name and a latitude. This batch also creates the Tropics view, which lists places that are in the tropics (i.e., places that lie between 12° north and 12° south of the equator). The Places table contains a column named Lat that uses the Latitude type. You insert latitudes into the Places table by using the CONVERT() function. The Tropics view uses magnitude comparisons of latitudes to determine which rows contain values that are in the tropics range.
Prev. page
[1]
2
3
next page 
