Posted by Erland Sommarskog on 07/06/05 13:43

David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> Why do you use UDTs for parameters? I've no idea if there's a performance
> difference but UDT rules and defaults don't get applied to variables so I
> see little or no benefit in using them.
>
> UDTs, rules and defaults are backwards compatibility features. Going
> forward, it is highly recommended that you use constraints instead - much
> more powerful and easier to use.

I'm sorry, but I think this is very poor advice.

First, user-defined data types are by no means a deprecated feature. (In
SQL 2005, Microsoft has added proper DDL syntax to create them.) User-
defined data tyepes are a not wholly satisfactory replacement for domains,
but it's definitely a useful feature. In the database I work with, about 99%
of the character columns are defined through some user-defined data type.

Take an example: say in your database there is a code, which appears in
many tables, and there are plenty of parameters and variables in stored
procedures etc. Say that this code is five characters long, and you now find
that you need to make it longer, say eight chars. If you use a UDT, there
is one file you need to check out and change. If you have created all the
columns as varchar(5) declared all parameters and columns etc as, you
have tons of work to find all references, and you can never be really sure
that you missed one references somewhere. (To make it really worse, assume
that you have several other varchar(5) entities that should not be changed.)
Even with a UDT you have a lot of work to compose a script to implement the
change in the tables. But this is a script that you can run and test, and
you can be sure that once it passes the tests it's OK. If you have a
plain varchar(5) you may find that the response to your customer is
"sorry, we cannot implement this change safely".

Second, rules and defaults are indeed deprecated by Microsoft, but that's
sillyness from their side. Say that you have a type column, which permits
the values A, B and C, and this type column appears in several tables.
If you define a rule and bind to the type, it's a very simple matter to
permit the value D as well. If you have constraints, you have a number of
objects in the database saying the same thing. Binding rules and defaults
to a user-defined data type is very powerful feature in SQL Server.
(Bind rules and defaults directly to columns is another matter; in this case
constraints are better.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]