Howto test SQL statements and ensure their quality?

Question

I am working on some business intelligence reports these days. The data is fetched by ordinary SQL SELECT statements. The statements are getting more and more complex.

The data I want to report is partially business critical. So I would feel better, if I could do something to proof the correctness and quality of the SQL statements.

I know there are some ways to do this for application-code. But what can I do to reach these goals at SQL level?

Thanks in advance.

Answer 1

I'm not aware of any SQL-level proof of QA you could do since you are looking for the intent (semantics) of the query rather than syntactical correctness.

I would write a small test harness that takes the SQL statement and runs it on a known test database, then compare the result with an expecetd set of reference data (spreadsheet, simple CSV file etc).

For bonus points wrap this in a unit test and make it part of your continuous build process.

If you use a spreadsheet or CSV for the reference data it may be possible to walk through it with the business users to capture their requirements ahead of writing the SQL (i.e test-driven development).