SQL Data Compare - Some tables missing

Question

When doing a Data Compare using SQL Server Data Tools through VS 2013 pro, I have a scenario where some tables appear to be getting missed out.

What I mean by that is there is data in TableA on the source server but no data in the equivalent table on the destination server.

However the results window doesn't even display a row for TableA.

Also if I try to filter the results in the next step prior to pressing "Finish", TableA doesn't appear as an option to filter by. It's almost as if the table doesn't exist on the destination server. I've verified that is does by:

• Connecting through SSMS and viewing the list of tables
• Running SELECT * FROM information_schema.tables

In both cases I can see the table is listed.

Has anyone else seen this? What am I missing?

Femineity

Data can be compared only if you know what records from tables must be compared. Compareer uses PK to know what records to compare. If your table don't have a PK (or at least a unique index) it ill be missing from the tables list – Alitta

Thanks Jean, that was the issue. I'm working with a DB provided by someone else and hadn't spotted that there was no PK on this table (there were actually a few others with the same issue too). Add your comment as an answer and I'll accept it. – Femineity

Answer 1

Data can be compared only if you know what records from tables must be compared. Comparer uses PK to know what records to compare. If your table doesn't have a PK (or at least a unique index) it ill be missing from the tables list.

You can solve it by creating a PK yourself (just for comparison)

EDIT

1. A coworker got a hard time recently due to someone explicit excluding some tables from the comparison project and committing it to git. So check it if it's not a new project.

2. I recently was tasked to compare tables without PK and found HASHBYTES to be a new friend, also there are not unique rows in the tables and to solve it I used ROW_NUMBER with PARTITION, see below snipet.

    SELECT Row_number() 
             OVER ( 
               partition BY [hashid] 
               ORDER BY [hashid]) AS RowNumber, 
           * 
    INTO   [dbo].[mytable_temp] 
    FROM   (SELECT Hashbytes('SHA2_512', (SELECT x.* 
                                          FOR xml raw)) AS [HASHID], 
                   * 
            FROM   [dbo].[mytable] AS x) AS y 

    go 

    ALTER TABLE [dbo].[mytable_temp] 
      ALTER COLUMN [hashid] VARBINARY(900) NOT NULL 

    ALTER TABLE [dbo].[mytable_temp] 
      ALTER COLUMN [rownumber] BIGINT NOT NULL 

    go 

    ALTER TABLE [dbo].[mytable_temp] 
      ADD CONSTRAINT pk_id PRIMARY KEY ([hashid], [rownumber]) 

    go 

That way I can create PK based on a hash calculated with the entire row content.

Obs:. Note I'm comparing MyTable_TEMPand not MyTable. that way I can leave it untounched.

Alitta

I have the same issue but the table does have a PK? It has a PRIMARY KEY CLUSTERED – Debidebilitate

Both tables must get some kind of "unique" column/index, Usually (idealy) a PK is unique. A CLUSTERED index is probably unique – Alitta

Microsoft should give some indicator/message that certain tables need primary keys in order to compare. The user needs more feedback on the output screen, because this was not clear when we ran the comparison. Thanks for this answer! – Wheeler

Answer 2

The tool is case sensitive. I had to rename the schema from core to Core to get tables in that schema to show up since the other database had the name Core.

Festal