With this discussion here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70328
I started to thinkn about Microsoft really calculated checksum value.
This code is 100% compatible with MS original. That is, the result is identical.
You can use it "as is", or you can use it to see that MS function does not produce that unique values one could expect.
With text/varchar/image data, call with SELECT BINARY_CHECKSUM('abcdefghijklmnop'), dbo.fnPesoBinaryChecksum('abcdefghijklmnop')
With integer data, call with SELECT BINARY_CHECKSUM(123), dbo.fnPesoBinaryChecksum(CAST(123 AS VARBINARY))
I haven't figured out how to calculate checksum for integers greater than 255 yet.CREATE FUNCTION dbo.fnPesoBinaryChecksum
(
@Data IMAGE
)
RETURNS INT
AS
BEGIN
DECLARE @Index INT,
@MaxIndex INT,
@SUM BIGINT,
@Overflow TINYINT
SELECT @Index = 1,
@MaxIndex = DATALENGTH(@Data),
@SUM = 0
WHILE @Index <= @MaxIndex
SELECT @SUM = (16 * @SUM) ^ SUBSTRING(@Data, @Index, 1),
@Overflow = @SUM / 4294967296,
@SUM = @SUM - @Overflow * 4294967296,
@SUM = @SUM ^ @Overflow,
@Index = @Index + 1
IF @SUM > 2147483647
SET @SUM = @SUM - 4294967296
ELSE IF @SUM BETWEEN 32768 AND 65535
SET @SUM = @SUM - 65536
ELSE IF @SUM BETWEEN 128 AND 255
SET @SUM = @SUM - 256
RETURN @SUM
END
Actually this is an improvement of MS function, since it accepts TEXT and IMAGE data.CREATE FUNCTION [dbo].[fnPesoTextChecksum]
(
@Data TEXT
)
RETURNS INT
AS
BEGIN
DECLARE @Index INT,
@MaxIndex INT,
@SUM BIGINT,
@Overflow TINYINT
SELECT @Index = 1,
@MaxIndex = DATALENGTH(@Data),
@SUM = 0
WHILE @Index <= @MaxIndex
SELECT @SUM = (16 * @SUM) ^ ASCII(SUBSTRING(@Data, @Index, 1)),
@Overflow = @SUM / 4294967296,
@SUM = @SUM - @Overflow * 4294967296,
@SUM = @SUM ^ @Overflow,
@Index = @Index + 1
IF @SUM > 2147483647
SET @SUM = @SUM - 4294967296
ELSE IF @SUM BETWEEN 32768 AND 65535
SET @SUM = @SUM - 65536
ELSE IF @SUM BETWEEN 128 AND 255
SET @SUM = @SUM - 256
RETURN @SUM
END
Peter Larsson
Helsingborg, Sweden
CHECKSUM replacement for IMAGE and TEXT
Posted - 08/22/2006 : 08:49:34
