January 31, 2010 at 1:38 am
Hi I am using SQL data Compare to on a database one is dated 21 and the other is 23 jan.
I am not able to compare a few tables so it specifies that I explicitly give a custome comparison key
a couple of my tables dont have primary keys or any otehr unique keys which column should i select in this case.
January 31, 2010 at 1:45 am
You should select a column or a set of columns that you can use to compare the data in the two tables. Usually this column or a set of columns would act like a primary key.
I have only used SQL Data Compare once. So Hopefully, someone with more experience with the tool will be help you out. But in the meanwhile try the above.
And the RedGate support site talks about comparison key and what are good candidates. Check it out if you havent already.
January 31, 2010 at 1:51 am
thanks for the prompt reply
Well I am just selecting one column randomly as the comparison key but there is a row in the UI of SQL Data Compare that states Columns in Comparison which by default takes all the columns.
but my concern here is that in few tables i have null values in the coumns that I am selecting and in some the data in those columns repeat in different records.
A bit puzzeled as to how SQL Data Compare compares the data in the tables does it scan through each data in revry cell ie does it compare every column of each record or does it just just pick up each record with the comparison key???
January 31, 2010 at 11:25 am
Please choose you comparison key carefully.
mohammed.arsalan (1/31/2010)
A bit puzzeled as to how SQL Data Compare compares the data in the tables does it scan through each data in revry cell ie does it compare every column of each record or does it just just pick up each record with the comparison key???
Logically, I would assume it would compare all the column values from the two tables based on the Comparison key column(s).
For Modified Rows
It will be like a join on the comparison key and find records where corresponding either match or don't.
For New Rows
It will be where the rows don't exists in the table for the comparison key. Think of like a Sub query where Comparison NOT IN.
Physically, the implementation would be using some complex algorithms for obviously better performance.
Does that answer your question.
January 31, 2010 at 10:50 pm
I dont know exactly but it compares table records even if I select a row with NULL values so I can say that its not only using the perticular value for the join
Logically it should be like this but if possible i need some insight from any one who has used SQL Data Compare and is sure about the internal working
At the moment i need to make some decisions on my dta integrety
February 2, 2010 at 12:37 am
mohammed.arsalan (1/31/2010)
I dont know exactly but it compares table records even if I select a row with NULL values so I can say that its not only using the perticular value for the joinLogically it should be like this but if possible i need some insight from any one who has used SQL Data Compare and is sure about the internal working
At the moment i need to make some decisions on my dta integrety
You MUST select a column that ensures accurate comparison. The appropriate column should be a key column. If no key column exists, you must select a combination of columns.
The only time you can get away with not choosing an appropriate column is if one table is empty and you are populating it. A good set of columns to use would potentially include a date column and a candidate key.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply