User Defined Types
SQL Server has supported User Defined Types for a long time. With the release of SQL Server 2008 Microsoft introduced User Defined Table types which could be used as input parameters to stored procedures. This allows you to pass sets of data as input rather than having to use XML or strings which are parsed into table data.
Still, the original user defined types are supported. I recently was watching a presentation by a developer where he was showing a Social Security Number (a unique identifier used by the USA Social Security Administration) as a specific 9 character fixed width (CHAR(9)) column in a table. It was at that time I was reminded of a few things.
1) In today’s need for security it is not a good practice to store a government supplied unique identifier in clear text inside your database. This kind of data should be encrypted before reaching your database, and decrypted after retrieving it, should the need arise to be able to view the actual contents.
2) User defined types are a great way to enable consistency on data stored in your database for which you require specific attributes.
A good example would be the North America dialing code for telephone numbers where you have the following format (###) ###-####. There are a lot of rules that could even be enforced should you wish to do that. For example, the number 1 can never be in the first or fourth digit. Another rule is that only numeric values are allowed.
So, you could define a user defined type for a North America telephone number and use that user defined type in table definitions resulting in consistent enforcement of your rules and definitions.
The problem I find with using any user defined type is that it is specific to only a single database. You have to define it in each database in which you wish to use your UDT. Moreover, a UDT is not transferrable from one database to another. You can’t defined an UDT in one database, and then use it in another database.
Do you use User Defined Types in your systems? How have they made your job easier? Share your experience in email to btaylor@sswug.org, or leave a comment here online.
Cheers,
Ben