SQL Server User Defined Data Types, Rules and Defaults

Problem

SQL Server provides numerous system data types to store dates, character based data, numeric data, etc. However there are some situations when a customized data type is needed for consistency across an application. Some examples could be phone numbers in a specific format, alpha numeric employee ID's, IP addresses, etc. What options are available to store this data in a unique manner?

Solution

Although a few different design options are available, one option is to create SQL Server user defined data types and bind them to an existing default and rule. In some respects, they can be considered customized SQL Server data types. The SQL Server user defined data types can be created both with SQL Server Management Studio and T-SQL commands. Let's walk through samples of each option to serve as an example of SQL Server user defined data types can be used with defaults and rules.

Next Steps

Creating and using your own defined data types with rules and defaults in SQL Server will provide a powerful mechanism to implement uniformity and consistency in your database design.

Keep in mind that a user defined data type in SQL Server is only accessible in a single database as such, it could make sense to script out, create the objects in dependent databases and update the database design.

One trick with new databases is to create the objects in the model database, so as new databases are created the user defined data types, rules, defaults, etc. will automatically be available.

Hi Barun, The variable @phone is being used while creating rule in the article. According to general rules it is unlogical to use a variable without first declaring it. But while creating a rule in SQL Server we may use one variable without declaration. However it should be prefixed by @ sign. It directly refers to updated or inserted value for which rule is being applied. So there is exception in case of creating SQL Server rule and you may use a variable in rule without declraing it.

Please also take in account that this feature may not be available in coming versions of SQL Server. According to BOL

"This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use check constraints instead. Check constraints are created by using the CHECK keyword of CREATE TABLE or ALTER TABLE."