Database Layer Testing

Recently I faced a problem: we had one old complex procedure taking more resources of the server. It had high number of executions, high duration and high reads. Constantly in our weekly reports we see it. At one point, we decided to rewrite the procedure. Even though reading the code and understanding the logic was a big challenge, the biggest challenge was testing it. While we were fine tuning the procedure and make it execute faster, Dev and QA gurus jumped in and identified the areas this procedure was called. As it is called by few core-components and they are used by many systems, QA team said it will take a tremendous effort to test all subsystems.
We came up with a new strategy: I should admit that even though it is yours truly who came-up with the idea, I am sure it came from above. I am not that smart to get something like this just like that
As the core-component cannot be tested directly, we decided to test the procedure directly: For all possible parameters which points to a column in the table we decided to execute both old and new versions of the procedure and compare the results. Unfortunately, the combination of parameters and the representation in the database turned the number of combinations to thousands.
To overcome the time needed for testing we decided to automate it. This is how we did that:

• Create two tables with the same set of columns. The columns name, data type and order should match the order of the output.
• Add additional columns to hold parameters.
• Add additional column to hold return value.
• Add an additional column for row id (we used bigint identity).
  Return value and row id are the only columns which have NOT NULL set to true.
• Create a script which will do the following:
1. Execute the old and new procedures for all possible parameter values and store the data with parameter values.
2. Immediately update the table with the return value and parameters.
   Finally compare the tables for any differences. SQL Compare or TableDiff could be used for this purpose.

For this method to work two conditions should be met:

1. The signatures of the procedures should be identical. The names, order and data types of parameters (input) and results (output) of the new procedure should be identical to the old one.
2. The results should be deterministic. That means the definition of the output should be static. Whatever the value of the parameters are the column names, data types and ordinal position should remain static.

We wrote a script that generated the execute statements which inserted the results into respective tables. At the end we compared both tables and they were exactly the same.
So we concluded that the results were identical. We were able to produce the results and the management was convinced that we do not need additional QA testing. This saved weeks of QA efforts.

Do you think that you need the script which can do that? Unfortunately I do not have a script which can do that magic for all procedures but I am ready to help you in creating one to meet your needs. Please drop me a note :)