Take the 2-minute tour ×
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.

POST ANSWER UPDATE

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)

AS
BEGIN

    SET NOCOUNT ON;

    -- 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])
        SELECT DISTINCT
            '[MyDatabase].[' + S.name + '].['
            + T.name + ']' AS [fullname],
            T.name AS [name],
            0 AS [chksum]
        FROM MyDatabase.sys.tables T
            INNER JOIN MyDatabase.sys.schemas S ON T.schema_id = S.schema_id
        WHERE 
            T.name 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])
        SELECT DISTINCT
            '[MyLinkedServer].[MyDatabase].[' + S.name + '].['
            + T.name + ']' AS [fullname],
            T.name 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
        WHERE
            T.name like IsNull(@TableName,'%');;

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

    DECLARE @TargetStmt VARCHAR(MAX)
    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

    DECLARE @SourceStmt VARCHAR(MAX)
    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

    SELECT  TT.FullName AS [TABLES WHOSE CHECKSUM DOES NOT MATCH]
    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;

END
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 6 down vote accepted

Here's what I've done before:

(SELECT 'TableA', * FROM TableA
EXCEPT
SELECT 'TableA', * FROM TableB)
UNION ALL
(SELECT 'TableB', * FROM TableB
EXCEPT
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.

Added:

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:

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableA

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableB

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
1  
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

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.

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

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

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

http://msdn.microsoft.com/en-us/library/ms173784.aspx

Something like this:

SELECT BINARY_CHECKSUM(*) from myTable;
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

Your Answer

 
discard

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.