Checksum pains

Are you using TSQL Checksum function in MSSQL2005? If yes be carefull with decimal (numeric) types. Try this example:

SELECT CHECKSUM(1000.0)
,
CHECKSUM(100.0)


Should not be there different values? Of course. Retype above SQL into following cast:

SELECT CHECKSUM(CAST(1000.0 AS VARCHAR(50)))
,
CHECKSUM(CAST(100.0 AS VARCHAR(50)))


or you can use other examples. The positive is that casting takes very small performance points:

DECLARE @i DECIMAL(20,5), @c INT
SET @i=0;

WHILE (@i<500000)
BEGIN
SET @i=@i+1; --7s

SET @c=CHECKSUM(CAST(@i AS VARCHAR(50))); --10 s
--SET @c=CHECKSUM(CAST(@i AS FLOAT)); --10 s
--SET @c=CHECKSUM(CAST(@i AS FLOAT)); --10 s
--SET @c=CHECKSUM(@i + CAST(0 AS FLOAT)); --10 s
--SET @c=CHECKSUM(@i); --10 s

END;