Tables not Showing up for comparision

jgstone

I have got two databases on that has data in it and one that does not. I only have about 10 tables per database and the schema is identical as the empty db was a copy of the real ( I manually deleted all the data ).

When I try and do a comparison only 3 of 10 tables show up...what can I do? I have looked at other posts and tried averything they said but I still get the problem.

Is there a cache that I can clean?
why wouldnt it compare identical databases? The only difference is one has data and one does not!!

Peter

Hi,

I've run into the same problem. For me the problem was that SQL Data Compare can work only with tables that have comparison keys. Copy from help:
"SQL Data Compare can compare tables and views only if they have:
...
for tables, a primary key, unique index, or unique constraint that is matches in both databases; for views, a matching unique, clustered index".

This is logical, but on the other hand it could give you an option to include tables without comparison keys if you specify columns manually. In the second screen of the "New comparison wizard", where you select tables and views for comparison, the list of tables could include also all tables without keys, but this tables should be unselected by default. You could select them only if you select comparison columns first.
This functionality for manualy selecting comparison columns is already there, so I believe that this new feature might not be so difficult to implement.

Something very similar is for example possible in SQL Delta db comparison tool, which I also tried, but at the end I decided for Red gate bundle, because I found many other, more important benefits with this tools. But having this feature in Red Gate data compare tool would really save me a lot of trouble...

jgstone

The second screen does allow me to select a comparision key, however I only have one option for each. This is probably normal because there is only one primary key set.

This is where I had an epiphany and decided to double check the db admins setup of the db. Come to find out he did not give most of the tables a primary key.

Thanks for your help but the problem is now solved

Peter

Nice to hear that you solved the problem.

But for Red gate, I hope they read the thread and that they will look into this for future versions. I see no reason why you shouldn't be able to compare tables without PKs, if you manualy select the comparison columns.

Red Gate? Anyone?

James Moore

Hi Peter,

Version 5 of SQL Data Compare supports custom comparison keys as you describe.

V5 is current in public beta and availible from (http://www.red-gate.com/messageboard/viewforum.php?f=35 - You might need to be registered and logged in to see the forum) If you could take a look at the new functionality and let me know if it does everything you need then that would be great.

Many thanks,

- James
--
James Moore
Red Gate Software

Peter

Just found out that this feature will be in SQL Data Compare 5. Great!

"SQL Data Compare 5 features:
....
- You can now compare tables where there is no primary key by using a custom key
...."

---

Peter Reskovic
Hermes SoftLab

richardjm

You might also want to note though that if you use a custom key you get no speed advantage that you would by using an index. This of course will only really affect you if you are comaring very large tables.

Peter

I've just tried data comparison of tables without PKs in Data Compare 5 beta and it works perfectly for me. Great work!

Thank you for your support.

---

Peter Reskovic
Hermes SoftLab

Brian Donahue

I second Richard's comment. Data Compare 5's ability to let you specify a column or columns to use to identify rows of data does not create an index on the table so I'd imagine you'd really want to put some indexes on the big tables. Not only for the data comparison performance, but also the data update performance.

The server's execution plan will locate rows to update using pointers in the index rather than plodding its' way through the whole table and all of its' data looking for the row to update.