Sql Compare Database Table Content
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.
List Database Tables Sql
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.
Sql Database Compare Tool
Free. Well tested. Flaws. Command line. Lots of parameters and options that need to be filled out and reviewed. 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. I’ve tried to use TABLEDIFF.exe for comparison.
Compare Sql Table Data
From memory, there were other limitation such as you can only compare a table as whole because the comparison tables need to be almost like-to-like. This is all fine except when you have column which you do not want to compare i.e. Timestamp column The kicker is that TABLEDIFF uses some cursor-type behaviour behind the scene for doing comparison. So, when comparing a very large table it would take hours and hours.
In both cases above, I’ve used EXCEPT clause to exclude columns which cannot be compared (binary, etc) and it performs faster than TABLEDIFF (not running in cursor-type loop). TABLEDIFF is OK but I’m finding its use cases quite limited due to the restrictions. I also use the EXCEPT clause.