is creating db user defined data types best practice?

Question

Is creating user defined types instead of using existing types best practice? In my previews work place all basic types were predefined, is it best practice? what advantages and what disadvantage it has.
Thanks a lot!

Answer 1

How cool are User Defined Data Types in MS SQL Server?

Personally, I don't use them but I've seen them. One problem is that client code recognises the base type only, IIRC, for MS SQL Server at least. And portability/behaviours across versions.

Answer 2

In MS SQL Server - the idea is great. However, there's a BIG but to them: you can't change them once they're in use, since there's no ALTER TYPE .... statement. This can be a major hassle.

Consider this: you have a bookstore app which has a user-defined type ISBN = VARCHAR(10) - works like a charm. Now the international committee decided to increate the ISBN field to 13 characters - unfortunately, there's no ALTER TYPE ISBNType..... so you're only choice is to basically drop all the columns of that type in all of your database, then re-create the type in its new form, and re-create all those columns again.

The idea is great and I would love to use it - but in its current state, it's next to unusable, in my opinion, which is unfortunate.

Marc