TSQL User Defined Types have been with me since I started writing code in SQL Server in the early 1990s. Even still, I have not used them very often, even though they can provide a lot of standardization around your data.
For those of you who haven’t used TSQL types, they work like this. You create your own type, based on an existing type, and extend its features or properties. SQL Server emulates this. For system names, instead of using NVARCHAR(128), a SYSNAME type is defined as NVARCHAR(128).
Here is a good example for the use of a user defined type. We constantly have tables with people and their names. Many times you will have a first name, and a last name. If you want your database to be consistent with every table containing a
First Name, or a Last Name, you can define these as your own user defined type.
The reason I have not used these often is that most of the systems I work with contain many databases. A User Defined Type is unique to the database in which it is defined, and may not be shared with another database. By keeping to the internal types supported by SQL Server, I find I don’t have to worry about a UDT being available in another database.
One way to make your UDTs more universal is to add them to your model database. Ever new database created will have the new type if it is already in your model database. Of course, this is not so easily shared with multiple users. In a large project with many developers managing database object, simply putting something in Model doesn’t always work. Still, in those large teams, they often share a centralized database definition or change control tool. Your User Defined Types could be sourced in the repository instead.
Probably more compelling to me are the UDTs you can create using CLR code. In that case you can really enhance the capabilities of your SQL Engine. Of course those data types will not be portable. If that is not a risk for your tools, then it probably is something you can consider.
Cheers,
Ben