I routinely denormalize so that I can enforce data integrity with constraints. One example is [a recent question on this site][1] - I replicate a column in another table, so that I can use a `CHECK` constraint to compare it to another column. Another example of this technique is [my blog post][2]. 

You cannot use `CHECK` constraints to compare columns in different rows or in different tables, unless you wrap such functionality in scalar UDFs invoked form a `CHECK` constraint. What if you actually need to compare columns in different rows or in different tables to enforce a business rule? 

For example, suppose that you know working hours of a doctor, and you want to make sure that all appointments fit within working hours? Of course, you can use a trigger or a stored procedure to implement this business rule, but neither a trigger nor a stored procedure can give you a 100% guarantee that all your data is clean – someone can disable or drop your trigger, enter some dirty data, and re-enable or recreate your trigger. Also someone can directly modify your table bypassing stored procedures. Either way you can end up with data violating your business rule without knowing about it.
 
Let me demonstrate how to implement this business rule using only FK and `CHECK` constraints – that will guarantee that all the data satisfies the business rule as long as all the constraints are trusted.

Yet another example is [a way to enforce that periods of time have no gaps and no overlaps][3].


  [1]: https://dba.stackexchange.com/q/15757/4839
  [2]: http://web.archive.org/web/20160506005831/http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/23/11318.aspx
  [3]: https://www.simple-talk.com/sql/t-sql-programming/contiguous-time-periods/