Verifying query results

Question

After you've written a complex query or stored procedure, how do you verify that the results are 100% accurate, and assure yourself that this will be the case for all input parameters?

My personal approach is to test, test, and test all the way through development - checking that NULLs do not appear where I don't expect them to, that rows do not get dropped or doubled-up, and then having completed the query I would perform 'an amount' of testing by taking random records from the resultset and then verify that these are as expected.

Answer 1

Complex (=hard to read) queries can often be written as a sequence of simple (=easy to read) queries run against a #temp table. During development I start by putting the series of simple steps in a stored proc, then I compare the results to the final query.

Answer 2

When writing complex queries, I first always write out my expected results for every set of inputs desired. Draw out your set of data and compile a matrix of results based on your inputs. As you start to build the complex query, you can ensure each piece of it is producing the correct results based on your initial test matrix.

I tend to keep my data set very 'simple' and minimal to ensure each specific case of filtering/result conditions are being met. Even though the SQL itself is incredibly complex, quantifying your results for validation during design and implementation is confusing and complicated and can lead to incorrect results without being aware. Only once I'm 100% confident against my sample data would I re-validate my results by testing against a staging scenario of production data (or a larger data set effectively).

Answer 3

Try all the subqueries separately first. Join your tables one at a time. Check the constraints in the database to avoid spending a lot of time on situations that can't occur (constraints are your friends). If the query is getting really hard, factor out parts of it that can be created as views.

Think.

Greatly underrated, thinking is. (That's probably because to a manager a programmer sitting there thinking looks the same as a programmer sitting there doing nothing.)