Live DB against DB backup doesn't let you set Comparison Key

DWolford1234DWolford1234 Posts: 44
edited November 2, 2007 10:32AM in SQL Data Compare Previous Versions
I just installed SQL Data Compare v
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.



  • Options
    I just saw the nice little triangle. I didn't realize before that it was a tool tip type icon. I got the message that you can't set custom compareison keys for backups.

    Could you explain a little why that is?
  • Options

    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 Chipperfield
    Red Gate
Sign In or Register to comment.