Published on

September 3, 2007

Pros and Cons of User Defined Types in SQL Server

When working with SQL Server, you may come across the concept of user defined types (UDTs). UDTs are a way to simplify the creation of the database schema and aid in standardization across the schema. However, there are both advantages and disadvantages to using UDTs that you should consider before implementing them in your database.

Advantages of User Defined Types

One of the main advantages of UDTs is that they allow you to couple a base type with a rule and default to represent a more complex entity. This provides a shorthand implementation and a standardized definition for a unit of business information in your database. By using UDTs, you can also build in data integrity by default, which is often lacking in non-DBA designed databases.

For example, you can create a UDT to represent gender, with predefined codes for different genders. This ensures that the gender field in your database is always populated with a valid value and follows an agreed-upon standard.

Disadvantages of User Defined Types

One major disadvantage of UDTs is their lack of portability between platforms, especially with the introduction of CLR data types in SQL Server 2005. If portability is important to you, using UDTs may not be the best choice.

Another drawback is the difficulty of altering UDTs. If you need to make changes to a UDT, it requires a great deal of planning and work. For example, if you decide to change the data type or nullability of a UDT, you may encounter issues with rules and defaults that were originally bound to the UDT. Unbinding and re-binding these objects can be a complex and time-consuming process.

Furthermore, using UDTs in temporary tables or table variables can be problematic. If a UDT has a rule or default bound to it, it cannot be used in a table variable. Additionally, you need to create the UDT in the TEMPDB before using it in a temporary table. This can lead to maintenance challenges, as you need to ensure that the UDT is present in all target servers and remember to update it in the MODEL database for future use.

Conclusion

While UDTs can be useful in certain scenarios, such as representing non-nullable date/time fields or numeric data types with default values, they may not be worth the trouble for most applications. The lack of portability, difficulty in altering UDTs, and limitations in using them with temporary tables are factors to consider.

If you do decide to use UDTs, it is important to carefully plan and document any changes to avoid potential issues. Consider the long-term implications and the impact on your database schema before implementing UDTs extensively.

Overall, UDTs can be a powerful tool in SQL Server, but they should be used judiciously and with caution.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.