Binding Rules With User Defined Types in SQL Server 2012

Here, I have provided an article showing you how to create a user-defined type and rules in SQL Server. Rules are constraints bound to columns from tables or to user defined data types. For example, zip code in the United States can be between 00501 and 89950. This can be done using the Rules. User-Defined Data Types are special types defined by users using an existing base data type. If you have programmed in languages like C, C++ and C# then you are probably familiar with the ability to provide a user-defined name to a known data type. Transact-SQL also provides you this option to create a user-defined data type and rules. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Creating a user-defined data type

Now press F8 to open the Object Browser in SQL Server Management Studio and expend it.

Database -> Programmability -> types-> Right click-> New-> User-Defined Data Types..

This would open the new user-defined data type window:

Now in the Name text box, enter a name of your choice. The name must follow the rules of names in Transact-SQL. In the Data Type combo box, select the data type of your choice. Of course, you must know what type you want to use and click OK Button. Now refresh the Object Browser to see the user-defined data type; click on the user-defined data types under the types folder.

How to test a newly created data type

Create a new table to use a user-defined data type (zip).

Create table Address

(

city varchar(20),

zipcode zip,

street char(27)

)

Creating a rule on user defined data types

The following example creates a rule that restricts the range of integers inserted into the column or columns to which this rule is bound.

Create rule zip_rule
as @zipcode >00501 and @zipcode<89950

Bind this column to the above range use the following statement:

sp_bindrule zip_rule, 'zip'

Here zip is the User Defined Data Type.

If we want to insert an integer value that is less than or greater than the range then it will not permit it and instead shows an error.

Insert into Address (zipcode) values (00450)

This range is less than the above range so it will not be accepted and it will show an error, such as:

Now take an another example which has the range between 00501 and 89950.

Insert into address (zipcode) values (00650)

The above statement will work fine with the condition.