Is checksum() guaranteed across SQL versions

Question

Is checksum() guaranteed across SQL versions

souLTower

SSCarpal Tunnel

Points: 4026
More actions
April 9, 2008 at 6:24 am

#186135

If I use checksum() in SQL2000 will I get the same results in 2005 and later versions.
EX:
select checksum('abcdefg') -- yields -2039903324
Thanks
ST

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

Heh... I don't think anything is guaranteed across versions, ST... but the same number is returned in both SQL Server 2000 and 2005 when I run your sample code of...

select checksum('abcdefg') -- yields -2039903324

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

souLTower (4/9/2008)
If I use checksum() in SQL2000 will I get the same results in 2005 and later versions.
EX:
select checksum('abcdefg') -- yields -2039903324
Thanks
ST

No, you will not.

the above statement is wrong See my post later in the thread.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

Andras Belokosztolszki (4/9/2008)
souLTower (4/9/2008)
If I use checksum() in SQL2000 will I get the same results in 2005 and later versions.
EX:
select checksum('abcdefg') -- yields -2039903324
Thanks
ST
No, you will not.
Regards,
Andras

Just as an example, I ran:

drop table foo

create table foo

(

a int primary key

identity(1, 10),

c varchar(10) default 'aaaa' COLLATE French_CI_AS,

b as checksum(a, c))

go

insert into foo

default values

go 10

select *

from foo

on 2000, 2005 and 2008, the checksum was returning different values on 2000, 2005 and 2008.

And this is wrong, see my post later

It may be that the difference is because there are multiple columns, but as Jeff mentioned, you should not assume that you will get the same number on the various server versions. You could use HashBytes, which should behave consistently.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

Andras Belokosztolszki (4/9/2008)
souLTower (4/9/2008)
If I use checksum() in SQL2000 will I get the same results in 2005 and later versions.
EX:
select checksum('abcdefg') -- yields -2039903324
Thanks
ST
No, you will not.
Regards,
Andras

How can you say that? I just ran ST's code in 2k and 2k5 and, all other things being equal, they both come up with the exact same answer.

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 5

Jeff Moden (4/9/2008)

How can you say that? I just ran ST's code in 2k and 2k5 and, all other things being equal, they both come up with the exact same answer.

Hmmm, you are right. I made a mistake.

In my example I should have used

b as checksum(a, c COLLATE French_CI_AS)

(my dbs had different collations, these affected the computed column collation, the collation affected the checksum).

My bad, apologies.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 6

Absolutely not a problem, Andras... thanks for double checking.

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

Here is how CHECKSUM is calculated and how you break it.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70832

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 8

Nicely done, Peter!

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 9

Peso (4/9/2008)
Here is how CHECKSUM is calculated and how you break it.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70832

Very Impressive!

Have you had a look at how MS handles a list of values?

CHECKSUM seems to do different things with collated strings (the same problem I got burned with)

select dbo.fnPesoTextChecksum('aaa' COLLATE French_CI_AS)

select checksum('aaa' COLLATE French_CI_AS)

seem to return different numbers.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software