locked
Why are rules deprecated? RRS feed

  • Question

  • So here's the problem - we have a defined domain for a percentage number that will always be { 1 >= x >= 0 }.

    First thought is to create a User Defined Data Type named "percentage" and attach a rule to it to enforce this domain.

    Looking in the Books Online, however, shows that apparently, "CREATE RULE will be removed in a future version of MicrosoftSQL 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."

    Surely this is a mistake? Why on earth would anyone consider having to remember to apply the same check constraint to every column in every table that uses this data type (and cope with the associated change control issues) when you could do this at the data type level?

    Can anyone shed some light on this for me?
    Wednesday, September 14, 2005 3:44 PM

Answers

  • Check constraints are more deeply understood by the system. Queries are modified to contain constraint logic during compilation, and this can help the query optimizer do a better job. For example, if you have a constraint that says column a is between 0 and 1, and you specify a >1, the optimizer will see the contradiction and use it to optimize the query if the constraint is known to have been enforced on all rows. The focus for enhancements related to constraints on column or row values going forward is on CHECK constraints. I agree it would be nice to associate the constraint with the column type rather than only the column. Longer term we'll consider adding that. But for now, it is wise to move toward check constraints.
    Wednesday, September 21, 2005 9:30 PM

All replies

  • One of the reasons they are likely going away is that they are non-standard (in ANSI SQL, Domains are used instead of Rules), another reason is probably because MS wants users to create UDT's in the CLR instead of UDDTs in the database.  I thought that UDDTs were phased out for 2005, but the online books make it sound like they'll be gone sometime in the future as you state (http://msdn2.microsoft.com/en-us/library/ms217475).

    The recommended approach is to create UDTs in a .NET assembly and use those new types.  Here is some of MS's documentation on the subject:
    http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnsql90/html/sqlclrguidance.asp#sqlclrguidance_topic4d

    Hope this helps,
    Josh Lindenmuth
    Thursday, September 15, 2005 11:57 PM
  • Thanks Josh.

    I hope you're wrong ;-)

    Creating a .Net assembly and a class for something as simple as a domain seems overkill to me, and I'll take some persuading as to the performance aspects, too.
    Monday, September 19, 2005 3:18 PM
  • Check constraints are more deeply understood by the system. Queries are modified to contain constraint logic during compilation, and this can help the query optimizer do a better job. For example, if you have a constraint that says column a is between 0 and 1, and you specify a >1, the optimizer will see the contradiction and use it to optimize the query if the constraint is known to have been enforced on all rows. The focus for enhancements related to constraints on column or row values going forward is on CHECK constraints. I agree it would be nice to associate the constraint with the column type rather than only the column. Longer term we'll consider adding that. But for now, it is wise to move toward check constraints.
    Wednesday, September 21, 2005 9:30 PM
  •  

    That is a nice description of why to use Check constraints instead of UDT's with Rules, but what about instances where a UDT would be a perfect fit for a Stored Procedure or a Function where Check Constraints aren't an option?

     

    For instance, I'm writing a Stored Procedure that creates Database Roles, I want to dynamically set my role name to the role name defined by the user, concatenated with the user's username. To do this, I need to enforce that the username is sysname compatible and also that the role name is sysname compatible while keeping both names smaller than half the size of a sysname. If they were both larger then my dynamic name would be too long and an exception would occur.

     

    So, in this instance, it would be perfect to have a UDT of nvarchar(64) with a rule specifying the same constraints that sysname enforces.

     

    Any suggestions of what to do for now?

    Friday, November 14, 2008 1:30 AM