Chapter 2. SQL Server tools for maintaining data integrity
You’ve probably seen it before: a client has called the help desk and reported that a couple of queries have seemingly inconsistent results. You’re called in to test the queries, so you start digging. You run two queries, and the results for one query don’t seem right, considering the other query’s results. So you start to wonder, “Is it my understanding of the schema?” You then start looking at the data model and the tables seem wonderfully designed. Your query should work. Dang, now what?
You dig deeper, and realize... the only objects you find in the database are tables. No constraints, no relationships, no nothing. A quick query later and you find that orphaned rows in a child table show up in one query but not another. At this point it’s clear that almost all of the data in these tables is suspect because if you can’t trust that a simple relationship works, can you trust that the child rows that exist are for the proper parent? Sadly, the answer is no, because the software may have created a new parent row that reused a key value. Now ideally you’ll test the consistency of the data every time you use the data, to verify that it’s correct. It doesn’t take a genius to determine how long before that gets old.
