how checksum works?

    1. Could you please me how checksum work in the SQL statement ? below is an example.

    SELECT * FROM Tablename1 WHERE CHECKSUM(*)

    NOT IN (SELECT CHECKSUM(*) FROM TableName2)

    2. and there is page checksum and database backup checksum, and what does it do while page checksum and database backup checksum?

    thanks!

     

  • For the above to work, TableName1 and TableName2 would need to have have the same structure.

    Since the structure is the same, why not use

    SELECT * FROM TableName1
    EXCEPT
    SELECT * FROM TableName2
  • 892717952 wrote:

    1. Could you please me how checksum work in the SQL statement ? below is an example.

    SELECT * FROM Tablename1 WHERE CHECKSUM(*)

    NOT IN (SELECT CHECKSUM(*) FROM TableName2)

    2. and there is page checksum and database backup checksum, and what does it do while page checksum and database backup checksum?

    thanks!

    There is no way that I'd use CHECKSUM() for any such comparison.  The risk of collisions is just too high and you can verify that even Microsoft says so by doing a search for CHECKSUM in SQL Server (which you should have done already but probably didn't do or you'd have already know this).  The document also makes a suggestion as to a better alternative.

    Remember also that as small as the possibilities of a collision get with the larger HASHBYTES algorithms, there is no guarantee of there not being collisions.

    What that means is that if you have an inequality, then they're guaranteed to be different.  If you have an equality, then they are NOT guaranteed to be the same and you would need a secondary check for such supposed equal values.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • DesNorton wrote:

    For the above to work, TableName1 and TableName2 would need to have have the same structure.

    Since the structure is the same, why not use

    SELECT * FROM TableName1
    EXCEPT
    SELECT * FROM TableName2

    thank you DesNorton!

  • thank you Jeff Moden!

    I want to the theory of checksum while using it as comparison condition.

  • Here's a bit of theory: https://www.online-tech-tips.com/cool-websites/what-is-checksum/

    HASHBYTES can use SHA1.

    This is a quick check, but not necessarily an absolute one.

  • EXCEPT  is great.   But to be clear, it doesn't require that the tables have identical structures, it compares the results of two queries.    You can select a subset of columns, combine columns from joined tables, and even do datatype conversions  (like int to varchar) in the queries.    Also, you can make the EXCEPT run much faster if you have a common key to join on for your second query.

    declare @Tbl1 table (RowID int primary key, Color varchar(30), ColorCode varchar(5))
    declare @Tbl2 table (RowID int primary key, Color varchar(30), ColorNumber int)

    insert into @Tbl1
    values (1,'Red','30'), (2,'Blue','35'), (3,'Yellow', '40'), (4,'Paisley','ABC')

    insert into @Tbl2
    values (1,'Red',30), (2,'Blue',35), (3,'Yellow', 45) -- No match because 45 != 40

    select RowID, Color, ColorCode
    from @tbl1

    except

    select t1.RowID, t2.Color, convert(varchar(5), t2.ColorNumber)
    from @Tbl1 t1
    join @Tbl2 t2 on t1.RowID = t2.RowID

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • The Dixie Flatline wrote:

    EXCEPT  is great.   But to be clear, it doesn't require that the tables have identical structures, it compares the results of two queries.    You can select a subset of columns, combine columns from joined tables, and even do datatype conversions  (like int to varchar) in the queries.    Also, you can make the EXCEPT run much faster if you have a common key to join on for your second query.

    declare @Tbl1 table (RowID int primary key, Color varchar(30), ColorCode varchar(5))
    declare @Tbl2 table (RowID int primary key, Color varchar(30), ColorNumber int)

    insert into @Tbl1
    values (1,'Red','30'), (2,'Blue','35'), (3,'Yellow', '40'), (4,'Paisley','ABC')

    insert into @Tbl2
    values (1,'Red',30), (2,'Blue',35), (3,'Yellow', 45) -- No match because 45 != 40

    select RowID, Color, ColorCode
    from @tbl1

    except

    select t1.RowID, t2.Color, convert(varchar(5), t2.ColorNumber)
    from @Tbl1 t1
    join @Tbl2 t2 on t1.RowID = t2.RowID

    Thank you for your kind help!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply