User-Defined (Aliased) Types

User-defined datatypes (UDTs) have been around since the earliest days of T-SQL. They're used to permit T-SQL developers to define a custom datatype that has specific Rules assigned there. Beyond that, they really serve no other (useful) function. You'll also find that the SQL Server BOL does not have much on aliased UDTsit's all been cobbled together with CLR UDT.

A T-SQL (aliased) UDT is just thatan object that dereferences one of the system T-SQL system datatypes. The SQL Server Management Studio IDE includes the ability to create T-SQL aliased UDTs in the IDE, but it's really pretty easy to do in T-SQL, as shown in Figure 2.63.

Figure 2.63. Creating a User Defined (aliased) T-SQL Datatype and Binding it to a Rule.

Looking a bit more closely at this script, we can see that the type "DiscountUDT" is created as an alias to the T-SQL float datatype. When this UDT is used in a table definition, any attempt to change the data in the column has to pass the criteria defined in the Rule bound to the UDT. Make sense? Notice that the @futureonly argument is set to 'futureonly'. This means that existing data in the database does not have to conform to the new Rule. Sure, if you want to revalidate existing data, set @futureonly to NULL. In this case, any column bound to the Rule or any column defined with the DiscountUDT and bound to the Rule is rechecked. As a result, the sp_bindrule can fail to execute until the database is fixed to make it compliant with the new Rule criteria.

That's Rules in a nutshell. Of course, Microsoft wants you to use CHECK constraints, and if you're worried about going out on a limb that Microsoft might saw off, you'll need to use CHECK constraints instead. No, they aren't as flexible as Rules and UDTs with Rules, but they're standard and, theoretically, are going to be supported longer. Hopefully, you'll be retired by the time Rules (or CHECK constraints) are dropped for something newer and better.