Have you ever created custom data types in SQL Server? A user defined data type is a data type you define based on an existing standard data type already included in SQL Server. User defined data types are created in a single database, and are not understood by other databases. The data must be converted back to the original SQL Server data type in order to transport the object.
Since you have to create your new user defined data type based on an already existing SQL Server data type, what is the added value for the effort on your part? Moreover, since the user defined data type is restricted in scope to a single database, is there still value in its use?
In my experience, the key value of a user defined data type is consistency. A good example might be the SYSNAME data type already built into SQL Server. At the core, the SYSNAME data type is a NVARCHAR(128) data type. It restricts the name to 128 characters, allowing variable length strings. It is used for many SQL Server object names such as Tables, Columns, Databases, etc. By using the SYSNAME data type you are assured to have consistency in values allowed, with a max length of 128 characters, at least as of SQL Server 2008R2. Later versions may have increased this constraint.
Not only can you restrict the scope of a user defined data type, as in VARCHAR(x) or DECIMAL(a,b), you can also constrain the value’s nullability. A user defined type can be defined to allow, or disallow null values. So, you can create a user defined data type that automatically does not allow null values when used in a table or as a variable.
You can also define default values and rules for your user defined data types.
The core value of the user defined data type, then, is consistency. You can create a type, provide a great deal of definition around it from data type, default values, rules and nullability, and be confident that everywhere it is found in your database the implementation is consistent.
Are you using user defined data types? If so, what kinds have you found useful? You can share your experience by leaving a comment below.
Cheers,
Ben