The best way to use rules in SQL Server is not to use them! Microsoft has told us the end is nigh for rules:

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

So, taking their advice, let’s go over how to use CHECK constraints instead. Think of a check constraint as a digital bouncer; just as a bouncer only lets people “on the list” into an exclusive club, a check constraint will only allow data that evaluates to TRUE for its conditions to be input. Now, bouncers aren’t perfect and neither are check constraints. This SQL Server TechCenter page gives some examples of check constraint shortcomings.

Still, let’s say I’ve got a table that holds CUSTOMER information, including Postal Code. You could very easily create a check constraint to verify that the Postal Code is a 5-digit numeric value:

ALTER TABLE Customer ADD CONSTRAINT chkPostalCode CHECK (PostalCode LIKE '[0-9][0-9][0-9][0-9][0-9]‘ );

A while back I used code similar to that listed here by John Sample to implement a check constraint for Order Data by Shipper. Using GeoCode functions to determine the distance between two points, I created a check constraint for a “same day” service based on a preset maximum distance. It’s not rocket science, but in this case we were already using GeoCode functions in other areas, and simple check constraints like these simplified processing and cut down on the amount of code needed to implement business rules.