My experience with UDTs has been in Sybase but I won't use them anywhere again.

I use to think UDTs where pretty cool about 9 years ago when I was told that was the standard at the company I was contracting with. Then the nightmare began.

The DBAs were always fighting battles when new UDTs were created to make certain they were proliferated to all databases on all servers. Quite a heroic effort. Then problems were encountered because what-you-see-is-not-what-you-get, that is, you may specify the data type in creating a table just to have it fail on some other database you replicate to because some how the object IDs of the UDTs are off.

Well, I'm still contracting with the same firm and they have come full circle to the extent of taking the hit on updating their extremely vast amount of schema to replace all UDTs with native data types. I like this a lot better. We do still use column naming conventions, though, which can help one to figure what general data type is used (e.g., col1_id, col2_nm, col3_amt, col4_tms...). This is most helpful when writing stored procs as you can pretty much figure out where you need to do a CONVERT without having to look at the data type.

I also must say I agree with Scorpian_66. If your programmers can't figure out how to keep the native data types in line, what makes you think they will use the right UDT? They're actually more apt to get things totally wigged out and you'll end up with a total mess with a rash of UDTs with the same native data types and no one will be able to figure out which one they should use.