I’ve been working on tuning a query for the last week and I’ve had to make several changes to the query’s logic. I’m always a little nervous when making those types of changes so I try to do some careful testing before I even suggest the changes to the developers. Who presumably at that point will put the changes into the test environment and run their own tests.
One of the tests I like to run is to generate a fair amount of output (10-50k rows) from both the original and the new queries (if possible), store them into temporary (not temp) tables, and then compare the two tables.
There are lots of ways to do compare the contents and structure of two tables but all have pros and cons.
Purchased tools (Sql Delta, Redgate, Idera etc)
- Benefits
- Nice GUI.
- Heavily tested so you know the result is correct.
- Flaws
- Costs money.
- Some companies don’t like you to install extra software on your machine.
- Typically these tools only compare two databases. You’d have to dump each table into a different database. Not that this is a big deal and there certainly may be ways to get the tools to compare two tables in the same database, I just don’t know it.
Roll your own
Ie write one T-SQL query to compare the structure of the two tables (this one will always be the same) and another to compare the data (new each time).
- Benefits
- Free.
- You get practice in writing T-SQL (yes, I’m stretching there.)
- Flaws
- Can take quite a bit of time to write. (Don’t forget to check for NULLs separately.)
- Mistakes happen.
- Performance may not be spectacular.
This one is new to me and is actually meant for use with replication. But it will work for our purposes.
- Benefits
- Free.
- Well tested.
- Flaws
- Command line.
- Lots of parameters and options that need to be filled out and reviewed.
Like I said. TABLEDIFF is new to me so I do want to point out a few options I think are important.
- -STRICT : By default there is some leeway on data types. For my purposes I don’t want to allow that.
- -b : Number of bytes to check on large data types. For our purposes here you may not need to check to make sure 1000’s of characters of data is all the same. You may be able to save some time with this one.
- -f : Script out changes to make the files match. There is an optional [filename] parameter to dump the script to a file. This is really helpful for trying to track down why your new script does do the same thing as the old one.
Filed under: Microsoft SQL Server, Performance, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, Performance, tools 
