Compare the output and speed of two stored procedures
Add a new window that compares the output (and speed) of two stored procedures using the same parameters (ignoring parameters that are missing on either side).
When fixing performance problems or adding new parameters to existing stored procedures, we often need to rewrite large portions.
After successfully speeding it up, we need to test if the results are still correct.
One way to do this is to compare the output of the new procedure with the output of the old one using different parameters. This involves storing results in temp tables, sorting them, output to a text file and using a compare tool.
Another scenario is when fixing a bug were results are duplicated by a bad join, or expected results are missing.
I have often been tempted to write an in-house tool to facilitate this, but it would be great if you guys have the time to do it.
A neat table-compare would also be nicer than comparing text in win-diff, especially when the procedure produces multiple results.
Jeff Humphreys commented
Might be in their SQL Compare tool? I've often wanted this. Also that could use the previous commit version as a before and after output. git lets you do this with the code, but the data side and performance side would be great. That and the plan costs.
But if they have data compare in another tool it might not be possible to include it in Prompt.