So you’d like more consistency in your database? Try SQL User-Defined Types (UDT).
The concept is simple. You essentially create a new, custom type based upon an existing type but with predefined properties.
For example, I know that in most of my databases, many tables will have an IP field. This will retain the IP of the user submitting data. The field in the database will always be a nvarchar(15) type to cater for the IPv4 format, i.e. 255.255.255.255.
If I have 10 instances of this field then I will have 10 repetitions of the type definition. I could create a UDT for IP which itself implements the nvarchar(15) type. Each time I define a new IP field on a table I can then use the IP data type.
The following SQL creates an email UDT:
Type defined like this will automatically be added to the type drop down menu of a column in a table’s design mode.
Consider using this for fields that need consistency, like:
Also I like to use this for fields that act as enum references. Often I will have an Id field in a table that references an enum, e.g. StatusId. These are always of the type tinyint, so I use a UDT called “enum”.
UDTs are especially useful if you have several developers working on a database – but only provided that they are all singing from the same hymn sheet.
Tags:
SQL
Published: 14 December 2010