I'm surprised that people are so against UDTs.

I tend to regard my database servers as flexible when they have to be, but I don't encourage changes to schemas unless absolutely necessary.

Frankly if your schema is in a regular state of flux and you are not in a development environment then there is something seriously wrong with the design or analysis behind the database. Possibly the DBA has been given the title because it is one he can spell!

I have a standard set of UDTs that I place in my MODEL database, hey presto, no more worries about UDT propogation when new databases are created. This included TEMPDB.

In addition, all my UDTs are created in a script, so it is easy to propogate them to every existing database on the server.

I tend to have types such as

• ty_int an integer that defaults to zero and does not allow nulls.
• ty_reqdDesc a 50 character NVARCHAR field that does not allow nulls or allow zero length strings
• ty_Yes a bit field defaulting to Yes
• ty_No a bit field defaulting to NO.
• ty_Today smalldatetime field I use for timestamping.

As for changing UDT's, what is so hard about ALTER TABLE tbl_section ALTER COLUMN SectionDescription ty_reqdDesc?

A schema change, which is implied by modifying UDTs, should be a rare and managed (planned) event.

You need scripts:-

• To propogate any new types.
• To drop any indices on the columns being changed.
• To drop any DRI using the columns.
• To apply your extra UDTs or refresh the old ones within the table
• To recreate indices

[*]To reinstigate DRI

In other words bread and butter DBA stuff.