Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Leveraging Constraint Evaluation Sequence in SQL Server Expand / Collapse
Author
Message
Posted Sunday, January 26, 2014 11:45 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:52 AM
Points: 1,403, Visits: 1,814
Comments posted to this topic are about the item Leveraging Constraint Evaluation Sequence in SQL Server

Thanks & Regards,
Nakul Vachhrajani.
http://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #1534854
Posted Monday, January 27, 2014 12:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 7:28 AM
Points: 8, Visits: 195
Thanks for an interesting helpful article.
Post #1534870
Posted Monday, January 27, 2014 12:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 27, 2014 12:37 AM
Points: 3, Visits: 15
This article touched on an important policy regarding logical planning for database design.

However, I am not really happy with 'generated rules' though for database systems.

This is because things like validation are not cast in stone and often subject to change. Where possible it is better to validate based on live tables of rules, using a flexible system rather than create static table constraints.

For example - a non blank user name constraint is a simplistic example in real life

A user name generally must be
1) Unique to a system ( not currently in use by another user )
2) Not containing extended characters or expletives
3) Minimum and maximum lengths, Not blank
etc etc

To create a constraint this complex, you are better off to to have a UDF or Proc, that looks up rules from tables, and provides a message about the problem.

I remember an example where a forum had to cull out the use of certain words in usernames that were insulting in another language. By having a table of 'illegal' words, the users could readily restrict usernames without having to have the DBA recreate a fixed constraint.

Flexibility and adaptability, combined with low maintenance design is important to time poor administrators and support staff.
Post #1534876
Posted Monday, January 27, 2014 1:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 28, Visits: 84
What good is it to know the evaluation sequence of constraints ?
- may be with the single exception of default constraints, in which special case microsoft would be well commanded to execute them first.
Post #1534884
Posted Monday, January 27, 2014 6:25 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:18 PM
Points: 128, Visits: 823
Thanks for doing so much work figuring out the order of constraints. I looked last week and could not find Microsoft documentation on it.

The problem is bigger. Triggers play into this mess. I have "Instead Of" and "After" triggers and trigger ordering on a single table in many cases. This firing order mess is also mostly undocumented. The "Instead Of" will fire the "After" triggers if you do a DML statement for that table inside of the "Instead Of" trigger. But, my questions ran deeper, wondering if some constraints ran before or after triggers. What rows are in "inserted" and "deleted" for each trigger?
Post #1534969
Posted Monday, January 27, 2014 6:34 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:18 PM
Points: 128, Visits: 823
Two more questions I've had for some time.

Are table and column check constraints actually different or is it just a semantic difference? And do they fire differently?

Do FOREIGN KEYS fire in any particular order? I use CASCADE DELETEs extensively.
Post #1534976
Posted Monday, January 27, 2014 6:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:37 AM
Points: 1,769, Visits: 2,143
Nakul,

Nice article. One small problem I noticed was that the constraint for being 18 years old was given as:

(DATEDIFF(YEAR,BirthDate,GETDATE()) >= 18);

That won't give the correct answer when the birthdate is later in the year than the current date. Wouldn't it be better to use:

(DATEADD(YEAR, -18,GETDATE()) >= BirthDate);


Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1534990
Posted Monday, January 27, 2014 7:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 284, Visits: 620
Bill Talada (1/27/2014)
Thanks for doing so much work figuring out the order of constraints. I looked last week and could not find Microsoft documentation on it.




Unfortunately, since the evaluation order as presented is not documented, Microsoft has no obligation to maintain it. Worse yet, there is no guarantee that the evaluation order is a a "real" order at all. It may be just what the optimizer picked for the toy example.

As it is, I do not feel that we can confidently rely on its conclusions.
Post #1534994
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse