David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:[color=blue]
> I could have said UDTs <emphasis> as implemented in SQL Server 2000
></emphasis> exist for backwards compatibility reasons but the OP had
> already stated he was using 2000.[/color]
I'm sorry, but I can't make any sense of this at all. User-defined data
types is a first-class citizen in SQL 2000 as well in SQL 2005. Any
talk about user-defined type existing only for backwards compatibility
is incorrect.
Note also the terminology: in SQL 2005 "user-defined type" is a
data type implemented in a CLR Assembly. The types that are in
SQL 2000 are called "user-defined data types", UDDT or "alias data
types" in a confusing mix. (Documentation has "alias", tools appears
still go with UDDT.)
[color=blue]
> I don't see that change control is any easier with a UDT than can be
> achieved with a data dictionary and proper naming conventions.[/color]
A type is something you can trust. Naming conventions is not. We have 3700
stored procedures, developed over a span of more than ten years. Should we
trawl them for naming conventions? That's not a serious suggestion.
[color=blue]
> As far as I can see UDTs make things much harder in the example you gave
> because you have to unbind rules and defaults, alter the column type,
> drop and recreate the UDT, alter the column type again and then rebind.
> I can't say I've tried this heavily in production systems but it seems
> like a lot more complexity and overhead than a single ALTER table
> statement. I'm interested if your experience is difference.[/color]
Well, this is how one of our type-definition files looks like:
EXEC sp_addtype 'aba_upduser', 'varchar(30)'
go
CREATE DEFAULT aba_upduser_def AS system_user
go
EXEC sp_bindefault 'aba_upduser_def', 'aba_upduser'
go
CREATE RULE aba_upduser_rule AS @x = system_user
go
EXEC sp_bindrule 'aba_upduser_rule', 'aba_upduser'
go
When we run this file, our load tool will automatically generate the
matching sp_drop and sp_unbind calls. Although, adding them manually is
no major task.
If we would need to change the type to nvarchar(128) that's a one-line
code change. Granted there are a couple of hundred tables to rebuild -
but at least it's a simple query to the system tables to find them.
(Or look up the cross-ref in DB-doc page for the type.) Running the
update script is another story.
Another example, say that we decide in 2005, that we want to use
ORIGINAL_LOGIN() rather than SYSTEM_USER. For us that a single file
to change and run. One single file.
With CHECK and DEFAULT constraints we would have several hundred tables
to change. Yes, we have a good tool for doing that. But we would have
to tailor our update scripts which are built on a principle that a
table change usually requires a reload.
[color=blue]
> Rules are deprecated by MS as you rightly say. This is a good thing for
> two excellent reasons. Firstly, rules don't offer anything like the
> functionality of CHECK constraints - specifically they can only
> reference a single column at a time.[/color]
I never said that you should only use rules. Rules are good when you
have the same behaviour in many columns all over the database. Constraints
are good when you have a behaviour which is peculiar to one table.
Since rules applies to a domain, the fact that they cannot interact
with other columns is a moot point. (Yes, occasionally there may be a
pair of columns that appear in many tables and they have some common
rules between them. But that is not common.)
Rules and constraints are not contradictory features, but they supplement
each other.
[color=blue]
> Even assuming you find some benefit in Rules you'll still need to use
> constraints as well so why persist your business rules in two different
> and potentially contradictory places?[/color]
Huh? I'm sorry, but you are not making any sense. You are advocating
that the same business rule should be duplicated in many instances over
the database - and with the possibility that the copies are different.
[color=blue]
> Secondly, the optimizer won't take advantage of rules as it will the
> equivalent constraints. This can make an big difference in some cases.[/color]
This is a correct observation. However, this presumes that the constraints
have never been enabled WITH NOCHECK, which unfortunately is the default
when you re-enable a constraint.
[color=blue]
> Given these two advantages I don't see any argument for rules at all.
>
> Defaults? I don't know what they offer that default constraints don't.[/color]
Again: they reduce the number of places you have to change, and reduce
the number of duplicate information.
[color=blue]
> Finally, an unquantifiable benefit. Most people I know follow the
> "official" line and consider these as legacy features. If people are
> using them less and less then I expect many of us will forget the
> syntax and inticacies of bound rules and defaults (I know I do). That
> means code we write today will be obscure to others in future and may
> make them less productive (as an example, look at the number of people
> who are still puzzled by the old *= syntax).
>
> I still think this is good advice. Maybe I should have explained my
> reasoning a bit better to start with so that the OP can make up his own
> mind.[/color]
You may not know and understand user-defined data types and bound rules
and bound defaults. But it's a huge leap to take it from there to tell
other people not to use it. I don't know DTS or Analysis Services, but
I would never dream of telling people not to use them.
--
Erland Sommarskog, SQL Server MVP,
Join Bytes!
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
