SQLServerCentral Editorial

In Praise of User-Defined Datatypes


User-defined datatype aliases assist in allowing clear readable code. Without them, you can still be consistent in your naming of a column, parameter or variable as 'price', but if you use, for example, a datatype called 'price', then your naming can be used to explain more about the way the column, parameter or variable is being used.

When used consistently, they help you to follow the lineage and flow of data through that rats-nest of procedures, functions and tables. You can categorise data. You can, if so requested, provide information about where sensitive information is being processed or stored and whether it is being properly curated. You can attach extended properties to them to further document their use.

They can also provide the means to discourage bad data, because you can use them to create data 'domains' that tie down specific application data, such as a postal code, as closely as possible to a definition. A date, for example, can be a wide range of values, whereas a date of birth has certain restrictions on it unless you are Methuselah or Count Dracula. An account code, an order code or a part number may all be stored as varchars but will have their own divergent rules and defaults.

We used to create data domains in Transact-SQL by binding types to Rules and Defaults (the similarly named 'default constraint' is restricted to columns). From 2005 onwards, both rules and defaults have been threatened with removal from SQL Server. Many of us still look at them longingly, as they languish with rather severe notices warning you not to use them. They are unloved because they are not in the SQL Standard, but part of Transact SQL as devised by Sybase before the ANSI SQL Standard came into being. However, if you're a SQL Purist, there are several other useful features that you'd have to jettison too.

You can argue that no domain datatype, such as 'product cost', will be in more than one table, so that it is enough to use table based NOT NULL constraints, CHECK constraints and DEFAULT constraints. The table, you could argue, defines the data domain. Well, no, not just because these are attributes, not entities, but because we also use temporary tables and table variables that need to be consistent. We have functions, batches and procedures that require parameters and that produce results that must be consistent too.

Of course, Rules have limitations. Since they aren't table-scoped, you can't use them to compare columns on a table, like you can with a CHECK constraint. Also, a rule is only checked when data is inserted or updated and can't check on existing data in a table. You can't bind a rule to a textntextimagevarchar(max)nvarchar(max)varbinary(max)xmlCLR user-defined type, or timestamp column. However, you still have constraints for doing this sort of task.

User-defined datatypes are still smiled-upon by Microsoft and while they deliver a great deal of value even without the use of Rules and Defaults, perhaps a reprieve for these two bad-boys wouldn't hurt, after 15 years on deprecation-row.