To ensure atomicity of transactions, we can use XACT_ABORT ON or wrap the transaction in TRY block and rollback in CATCH block. In some cases, the XACT_ABORT ON approach uses noticeably less CPU. I am posting repro scripts. Please run them, tweak them, and post your findings. Environment I've run my scripts on 2008 R2 Dev Edition. Snapshot ...

Wrapping related changes in a transaction is a good way to ensure data integrity. Besides, in some cases it just runs noticeably faster, using less CPU. As usual, I am posting repro scripts, which you can run, tweak, and see for yourself. Environment I've run my scripts on 2008 R2 Dev Edition. Snapshot isolation is enabled, ...

In SQL 2008 R2, MERGE does not implement foreign keys properly. I will show both false negatives (valid rows are rejected) and false positives - orphan rows that are allowed to save.   False Negatives   The following tables implement a very common type/subtype pattern: CREATE TABLE dbo.Vehicles(    ID INT NOT ...

We can tuck any logical expressions in CHECK constraints, but all foreign keys are currently capable of right now is to verify that one or more column values are equal. In many cases it would be very useful to have foreign keys use more complex logical expressions. Also in some cases the ability to create constraints on indexed views would ...

I will demonstrate how use constraints to make sure that intervals of time have no overlaps. Also you may ensure that there are no gaps either, but if you choose to allow gaps, then the query to retrieve them is very easy. As discussed in my previous posts, you can also use triggers or UDFs wrapped in CHECK constraints, but only trusted ...