Post reply

CHECKSUM

  • September 6, 2009 at 3:47 am

    #225855

    When I execute below SQL statement in SQL server 2000, I get the same value of 128414903 for the CHECKSUM in two different rows with different data. Why is that?

    DECLARE @checksum TABLE

    (

    CustomerNo CHAR(17)

    ,Address CHAR(40)

    )

    INSERT INTO @checksum

    SELECT '00000000014331462','A15 Block No.: 3' UNION ALL

    SELECT '00000000061051462','F23 Block No.: 5'

    SELECT CHECKSUM(CustomerNo,Address)

    FROM @checksum

    - Zahran -

  • September 6, 2009 at 12:23 pm

    #1049888

    which version of SQL Server 2000 you are running ? SP4? later ?

  • September 6, 2009 at 10:03 pm

    #1049964

    Please open BOL on CHECKSUM and read 2nd paragraph of "Remarks".

    _____________
    Code for TallyGenerator

  • September 7, 2009 at 12:45 am

    #1049994

    which version of SQL Server 2000 you are running ? SP4? later ?

    It's RTM version.

    - Zahran -

  • September 7, 2009 at 12:48 am

    #1049995

    which version of SQL Server 2000 you are running ? SP4? later ?

    It's RTM version.

    - Zahran -

  • September 7, 2009 at 12:51 am

    #1049996

    which version of SQL Server 2000 you are running ? SP4? later ?

    It's RTM version.

    - Zahran -

  • September 7, 2009 at 10:51 am

    #1050177

    Sergiy - the paragrah you mentioned change nothing. hash is a hash - and for a given string should always return the same value. did you try to run his query on your sql ?

  • September 7, 2009 at 3:19 pm

    #1050231

    Marcin Gol [SQL Server MVP] (9/7/2009)

    Sergiy - the paragrah you mentioned change nothing. hash is a hash - and for a given string should always return the same value. did you try to run his query on your sql ?

    You probably did not reach the last sentence of the paragraph.

    😉

    _____________
    Code for TallyGenerator

  • September 7, 2009 at 3:37 pm

    #1050233

    Hi Zahran

    Little quote from BOL

    When an MD5 hash algorithm is specified, the probability of HashBytes returning the same result for two different inputs is much lower than that of CHECKSUM.

    Keep in mind. A hash is as unique as possible, but it's not a guarantee for real uniqueness. If you have a VARCHAR(100) column it represents much more possible values than an INT.

    You can use HASHBYTES or BINARY_CHECKSUM to get a better hash than old CHECKSUM function.

    SELECT

    HASHBYTES('MD5', CustomerNo + Address)

    ,BINARY_CHECKSUM(CustomerNo, Address)

    ,CHECKSUM(CustomerNo, Address)

    FROM @checksum

    Anyway, a hash will never be a 100% guarantee for uniqueness. If you need unique values use an IDENTITY column or a GUID.

    Greets

    Flo

    Edit: Dang! Sorry, didn't notice this is the 2000 forum. Probably the new functions are not available...

  • September 7, 2009 at 4:09 pm

    #1050247

    Sergiy (9/7/2009)

    Marcin Gol [SQL Server MVP] (9/7/2009)

    Sergiy - the paragraph you mentioned change nothing. hash is a hash - and for a given string should always return the same value. did you try to run his query on your sql ?

    You probably did not reach the last sentence of the paragraph.

    😉

    i read it but i feel that we don't understand each other 😉

    my version: for a given string we should always receive same hash, did i say that hash is unique? nope.

    maybe other question is right here ... does the implemantion of checksum function has changed over the time ? (sql 2000 -> 2008?); if don't why im getting other values of checksum than Zahran ?

  • September 7, 2009 at 4:19 pm

    #1050250

    Marcin Gol [SQL Server MVP] (9/7/2009)

    Sergiy (9/7/2009)

    Marcin Gol [SQL Server MVP] (9/7/2009)

    Sergiy - the paragraph you mentioned change nothing. hash is a hash - and for a given string should always return the same value. did you try to run his query on your sql ?

    You probably did not reach the last sentence of the paragraph.

    😉

    i read it but i feel that we don't understand each other 😉

    my version: for a given string we should always receive same hash, did i say that hash is unique? nope.

    maybe other question is right here ... does the implemantion of checksum function has changed over the time ? (sql 2000 -> 2008?); if don't why im getting other values of checksum than Zahran ?

    It actually returns the same hash value for unchanged original value.

    Is it any different on you machine?

    And the topic was actually about uniqueness problem. My reference was about it.

    _____________
    Code for TallyGenerator

  • September 8, 2009 at 2:58 am

    #1050351

    Marcin Gol [SQL Server MVP] (9/7/2009)

    maybe other question is right here ... does the implemantion of checksum function has changed over the time ? (sql 2000 -> 2008?); if don't why im getting other values of checksum than Zahran ?

    I get the same return values on SQL Server 2008.

Viewing 12 posts - 1 through 11 (of 11 total)

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