I thought the article did a great job of explaining UDTs, and then explaining the pros and cons. I was aware of the pros, but not so aware of the cons.
I wanted to reply to the comment: "The real feature missing is the ability to modify the type and have it propagate to all the tables. With that one change they go from ugly to interesting."
My experience doesn't negate the above comment, but it does apply. I use ERwin (data modeling software capable of creating database-generating scripts) to generate my databases. I use ERwin not just the first time an application goes live, but most of the time I make changes to the database. The entire database is re-created from scratch, and the data is copied from the old database to the new.
I use the equivalent of UDTs within ERwin (in some situations, not for all columns). So, when I change a definition of a UDT within ERwin and then re-generate the database, the change is effectively propagated to all the tables. After reading the article and the comments, I think of my approach as a way to get the best of both worlds--assuming you can have the luxury of creating a production database from scratch when changes are needed. (Our databases are not 24x7. They are needed strictly during normal business hours.)