REPEATABLE READ

As a consultant, it's not uncommon for me to need to rewrite a poorly performing query or sproc without a full understanding of the underlying biz rules or context. Improved performance is the stated goal, but data fidelity is implied as critical.

Enter unit testing - something many of us rely upon heavily during 'normal' development at the application level. With sprocs/queries I've found two approaches that work well for unit testing.

1) Text Files: CTRL+SHIFT+F is your friend. Use it to 'burn' query results in either QA or MS to a .txt file, instead of sending results to Text/Grid. Just make sure to evaluate parameters in the targeted query/sproc, and burn a few result sets to disk as .txt files. (Make sure to keep track of which params correspond to which result sets). Then, tweak your sproc/query as needed, then rerun the same params, and output to corresponding text files. At this point, you can then just use a simple differencing application (I prefer to use WinMerge - free and awesome) to verify that your changes haven't impacted the logic.

2) SQL Data Compare: Red-Gate is your friend. (This is the approach that I typically use). Instead of 'burning' results to .txt files, 'burn' them to 'temp' tables in the DB. (By 'temp' I mean dbo.unittest__12a or something similar - not #temp, as I need the tables to appear for use by the Red-Gate tool.) Just make sure that each 'temp' table has a PK, and that you've created as many as needed to fully validate all of your parameters - then tweak the proc/query as needed, and then use SQL Data Compare to compare all of the tables used for your unit test. I prefer this route as it provides a very VISUAL way to quickly compare results.