Check Constraints On Date Ranges

Michael Swart has a script to check start versus end dates to ensure there is a valid check constraint for date ranges:

Don’t blindly run scripts that you got from some random guy’s blog. Even if that someone is me. That’s terribly irresponsible.

But this query may be useful if you do want to look for a very specific, simple kind of constraint that may match your business specs. These constraints are just suggestions and may not match your business rules. For example, when I run this query on Adventureworks, I get one “missing” check constraint for HumanResources.Shift(StartTime, EndTime) and when I look at the contents of the Shift table, I get this data:

If you wish non-blindly to run a script you got from that guy’s blog, click through for the script and more details, including a discussion of false positives.