Using the Server Explorer to Manage Types

SQL Server user-defined types have been around for quite some timesince the earliest versions of Microsoft SQL Server. These aren't really user-defined system types as much as aliases to system data types. That is, a SQL Server user-defined type (UDT) simply dereferences an existing SQL Server system data type. Sure, the Server Explorer can manage user-defined data typesbut only the new Common Language Runtime (CLR) data types. It does not see the more-familiar "aliased" types I've been using for decades. I think this is an obvious oversight.

A lot of attention has been drawn to CLR user-defined types in SQL Server 2005; I'm going to discuss this new approach to handling (very) special data typing in Chapter 13, "Managing SQL Server CLR Executables."

Because the Server Explorer does not help with SQL Server UDTs, you'll have to use the SQL Server Management Studio to manage them. You'll have to drill pretty deep to get to themthey're under <database> | Programmability | Types | User-defined Data Types. SQL Server Management Studio provides an easy-to-use dialog to create SQL Server UDTs, as well as a way to define (bind) a Default and Rule to the type (as shown in Figure 4.77).

Figure 4.77. Using the "New User-Defined Data Type" dialog to define a UDT.

You can save the UDT definition to a T-SQL script by clicking the "Script" button. Notice that I bound the new UDT to a Rule. Once you define a SQL Server user-defined type (as opposed to a CLR type), you can create a SQL Server Rule that applies to all uses of the type. For example, suppose you create a new "postal code" data type (I used SQL Server Management Studio to script an existing type to a new query window, as shown in Figure 4.78).

Figure 4.78. Creating a new user-defined SQL Server data type.

Note how the new type (dbo.PostalCode) is simply defined as a varchar(10). Once defined, I can create a SQL Server Rule to apply to this type. This Rule is applied each time data is added or changed in the column defined with this datatype. While you don't need to define a user-defined type (UDT) to apply a Rule, doing so means that you can change the Rule and have the change apply universally over the entire databasewherever the UDT is used to define the data type for a column. For example, our PostalCode data type has a formatting rule that dictates how the data in any column whose data type is PostalCodeit's shown in Figure 4.79.

Figure 4.79. Defining a Rule to dictate how U.S. postal codes are to be formatted.

IMHO

Ah, T-SQL Rules are not really supported by Microsoft, but keep reading. No, it's not like when politicians ignore rules....

Once the SQL Server Rule is defined, you have to bind the Rule to the column(s) or UDTs where it's to be applied. In this case, I want to bind (apply) this Rule to the ZipCode column of the Biblio.dbo.ZipCodeState table. I set up this table using the Table Designer, as shown in Figure 4.80.

Figure 4.80. Defining the ZipCodeState table.

Notice that the data type selected in the Table Designer was selected from a list of valid data typesthis list includes both SQL Server UDTs and CLR UDTs. I'll show you how to define these CLR UDTs in Chapter 13.

Binding a SQL Server Rule is not hardyou simply call the sp_bindrule system stored procedure and pass the rule and target, as shown in Figure 4.81.

Figure 4.81. Binding a SQL Server Rule to a SQL Server UDT.

From this point forward, whenever data is inserted or updated in any column whose data type is set to PostalCode, the Rule code is executed to validate the incoming value. If the data does not conform to the Rule criteria (if I may leverage that word), the insert or update is rejected and SQL Server returns an error. Of course, this means that if your application accepts postal codes from outside the U.S., you'll want to redefine your Rule to include other formatsor use another approach.

Before you run off and start coding and binding Rules to UDTs, be aware that Microsoft no long likes this approach (for some reason). The engineers up on the hill in Redmond (and Bangalore) have already deprecated Rules in the SQL Server Management Studio IDE, so you can't create new Rules with a templateas you can with other objects. For some reason, Microsoft wants developers to use Constraints instead. Unfortunately, Constraints can't be bound to UDTs. This means you'll have to create a Constraint and bind it to each individual column where it's to be appliedwhat a PIA (and I don't mean Primary Interop Assembly)! I'm also of the opinion that Microsoft will have to continue to support Rules foreverat least, in a "compatibility mode". If they don't, they're pooched, and so are a lot of SQL Server customers.