Live DB against DB backup doesn't let you set Comparison Key
DWolford1234
Posts: 44
Hello,
I just installed SQL Data Compare v 6.0.0.1124.
My first test, I thought I'd try something simple, so I used a backup of a very small db. It only has three tables in it, and as it turns out, it has no primary keys defined. NICE! It does, however, have an identity column defined for each table.
So I go to the Table & Views tab. It shows me my three tables and under comparison key, they all say Not Set. I hit the drop down arrow on the first table and get a grid that has a Comparison key drop down, which is empty, and in the Key column, there are no check boxes. I can highlight the column I want to use for a comparision key, but nothing happens.
I'm sure you can reproduce it easily by making a database with a couple of tables with no primary key; back it up, and then try the compare.
When comparing live against live, I can successfully choose the comparison key.
Thanks,
Dan
I just installed SQL Data Compare v 6.0.0.1124.
My first test, I thought I'd try something simple, so I used a backup of a very small db. It only has three tables in it, and as it turns out, it has no primary keys defined. NICE! It does, however, have an identity column defined for each table.
So I go to the Table & Views tab. It shows me my three tables and under comparison key, they all say Not Set. I hit the drop down arrow on the first table and get a grid that has a Comparison key drop down, which is empty, and in the Key column, there are no check boxes. I can highlight the column I want to use for a comparision key, but nothing happens.
I'm sure you can reproduce it easily by making a database with a couple of tables with no primary key; back it up, and then try the compare.
When comparing live against live, I can successfully choose the comparison key.
Thanks,
Dan
Comments
Could you explain a little why that is?
When you choose a comparison key, Data Compare uses this to determine the order in which to retrieve the rows, either from the SQL Server or the backup file.
If it's from a SQL Server, then it's just a case of constructing an appropriate ORDER BY clause, which means we can use anything you like, with the proviso that an ORDER BY matching an index will be significantly faster.
However, when retrieving from a backup file, we don't have the power of SQL Server to do the sorting for us, but rather have to do it ourselves. If we use an order that matches that of an index, we can deterministically return the rows from the backup without having to do a sort.
Allowing custom comparison keys would mean we'd have to do an in-memory, or more likely on-disk, sort of the table before returning any rows for comparison.
It's something we're considering for the future, but technically it'll be much more challenging to achieve in a way that allows us to get decent performance and maintain scalability.
Hope that helps,
Robert
Red Gate