Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We're doing an ETL process. When all is said and done there are a bunch of tables that should be identical. What is the quickest way to verify that those tables (on two different servers) are in fact identical. I'm talking both schema and data.

Can I do a hash on the table it's self like I would be able to on an individual file or filegroup - to compare one to the other. We have Red-Gate data compare but since the tables in question contain millions of rows each I'd like something a little more performant.

One approach that intrigues me is this creative use of the union statement. But, I'd like to explore the hash idea a little further if possible.


For any future vistors... here is the exact approach I ended up taking. It worked so well we're doing it on every table in each database. Thanks to answers below for pointing me in the right direction.

CREATE PROCEDURE [dbo].[usp_DatabaseValidation]
    @TableName varchar(50)



    -- parameter = if no table name was passed do them all, otherwise just check the one

    -- create a temp table that lists all tables in target database

    CREATE TABLE #ChkSumTargetTables ([fullname] varchar(250), [name] varchar(50), chksum int);
    INSERT INTO #ChkSumTargetTables ([fullname], [name], [chksum])
            '[MyDatabase].[' + + '].['
            + + ']' AS [fullname],
   AS [name],
            0 AS [chksum]
        FROM MyDatabase.sys.tables T
            INNER JOIN MyDatabase.sys.schemas S ON T.schema_id = S.schema_id
   like IsNull(@TableName,'%');

    -- create a temp table that lists all tables in source database

    CREATE TABLE #ChkSumSourceTables ([fullname] varchar(250), [name] varchar(50), chksum int)
    INSERT INTO #ChkSumSourceTables ([fullname], [name], [chksum])
            '[MyLinkedServer].[MyDatabase].[' + + '].['
            + + ']' AS [fullname],
   AS [name],
            0 AS [chksum]
        FROM [MyLinkedServer].[MyDatabase].sys.tables T
            INNER JOIN [MyLinkedServer].[MyDatabase].sys.schemas S ON 
            T.schema_id = S.schema_id
   like IsNull(@TableName,'%');;

    -- build a dynamic sql statement to populate temp tables with the checksums of each table

    SELECT  @TargetStmt = COALESCE(@TargetStmt + ';', '')
            + 'UPDATE #ChkSumTargetTables SET [chksum] = (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM '
            + T.FullName + ') WHERE [name] = ''' + T.Name + ''''
    FROM    #ChkSumTargetTables T

    SELECT  @TargetStmt

    SELECT  @SourceStmt = COALESCE(@SourceStmt + ';', '')
            + 'UPDATE #ChkSumSourceTables SET [chksum] = (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM '
            + S.FullName + ') WHERE [name] = ''' + S.Name + ''''
    FROM    #ChkSumSourceTables S

    -- execute dynamic statements - populate temp tables with checksums

    EXEC (@TargetStmt);
    EXEC (@SourceStmt);

    --compare the two databases to find any checksums that are different

    FROM #ChkSumTargetTables TT
    LEFT JOIN #ChkSumSourceTables ST ON TT.Name = ST.Name
    WHERE IsNull(ST.chksum,0) <> IsNull(TT.chksum,0)

    --drop the temp tables from the tempdb

    DROP TABLE #ChkSumTargetTables;
    DROP TABLE #ChkSumSourceTables;

share|improve this question
Is SSIS an option? It'd be fairly easy to read in the one table and do a lookup against the other. –  Kevin Feb 8 '13 at 1:56
It is an option, it's what is being used for the ETL process, but the mustaches upstairs want a second opinion on if it worked or not so using SSIS to prove that SSIS got it right is not as convincing as dropping fancy words like CheckSum or MD5 Hash. –  RThomas Feb 8 '13 at 15:48

4 Answers 4

up vote 9 down vote accepted

Here's what I've done before:

(SELECT 'TableA', * FROM TableA
SELECT 'TableA', * FROM TableB)
(SELECT 'TableB', * FROM TableB
SELECT 'TableB', * FROM TableA)

It's worked well enough on tables that are about 1,000,000 rows, but I'm not sure how well that would work on extremely large tables.


I've run the query against my system which compares two tables with 21 fields of regular types in two different databases attached to the same server running SQL Server 2005. The table has about 3 million rows, and there's about 25000 rows different. The primary key on the table is weird, however, as it's a composite key of 10 fields (it's an audit table).

The execution plans for the queries has a total cost of 184.25879 for UNION and 184.22983 for UNION ALL. The tree cost only differs on the last step before returning rows, the concatenation.

Actually executing either query takes about 42s plus about 3s to actually transmit the rows. The time between the two queries is identical.

Second Addition:

This is actually extremely fast, each one running against 3 million rows in about 2.5s:



If the results of those don't match, you know the tables are different. However, if the results do match, you're not guaranteed that the tables are identical because of the [highly unlikely] chance of checksum collisions.

I'm not sure how datatype changes between tables would affect this calculation. I would run the query against the system views or information_schema views.

I tried the query against another table with 5 million rows and that one ran in about 5s, so it appears to be largely O(n).

share|improve this answer
Why are you suggesting UNION instead of UNION ALL? Which duplicates do you wnat to eliminate? –  A-K Feb 8 '13 at 1:57
@AlexKuznetsov Fair enough, although I suspect that the query engine is smart enough to recognize that we're selecting the entire primary key, or may not process the distinct until it has the result set from the EXCEPT statements. I'm actually curious now if it is. It makes more logical sense to be explicit to the RDBMS, though, so I'll update it. –  Bacon Bits Feb 8 '13 at 8:58
The question is about 2 different servers though. –  ypercube Feb 8 '13 at 9:27
I don't have two servers to test on. :) –  Bacon Bits Feb 8 '13 at 9:35
The union all across a linked server approach had me worried. I like the aggregate and binary_checksum approach... going to start some testing with that now. –  RThomas Feb 8 '13 at 15:46

I believe you should investigate BINARY_CHECKSUM, although I would opt for the Red Gate tool:

Something like this:

share|improve this answer
Will this detect differences in the tables' schema (different column names or datatypes)? –  ypercube Feb 8 '13 at 9:33
This has potential. I'll do some testing and get back. –  RThomas Feb 8 '13 at 15:46
Gave you a plus 1, but the aggregate provided by bacon bits was the icing on top. Thanks. –  RThomas Feb 8 '13 at 16:27

Here are several ideas that might help:

  1. Try different data diff tool - have you tried Idera's SQL Comparison toolset or ApexSQL Data Diff. I realize that you already paid for RG but you can still use these in trial mode to get the job done ;).

  2. Divide and conquer - how about splitting tables into 10 smaller tables that can be handles by some commercial data comparison tool?

  3. Limit yourself only to some columns - do you really need to compare data in all columns?

share|improve this answer

If you have a primary key, this is sometimes a better way to examine differences because the rows that should be the same are shown together.

   ID = IsNull(A.ID, B.ID),
   AValue = A.Value,
   BValue = B.Value
   dbo.TableA A
   FULL JOIN dbo.TableB B
      ON A.ID = B.ID
      SELECT A.*

See it in a sqlfiddle.

share|improve this answer
Nice, thanks... good way to find differences after aggregate indicates differences exist. Thanks. –  RThomas Feb 11 '13 at 16:13

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.