How to Prevent Checksum() from generating Duplicate Values in sql server

Question

As it is known there is a probable chance of checksum generating duplicate values, and I am supposed to find an approach which prevents checksum from generating duplicate Values... Please help

Thanks n Regards

You can't and you shouldn't need to. If you want a unique identification, a finite-size value (a checksum) isn't the way to go. — user395760
– user395760, Commented Jul 28, 2011 at 9:17

gbn · Accepted Answer · 2011-07-28 09:13:11Z

2

You can't. CHECKSUM can never be guaranteed unique.

MSDN for CHECKSUM says to use HASHBYTES instead which has far less chance of duplications.

However, any hashing function can not guarantee 100% that you will have no duplicates. if you want "no duplicates" then you shouldn't hash...

answered Jul 28, 2011 at 9:13

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sll · Accepted Answer · 2011-07-28 09:15:21Z

1

Consider using HashBytes

From MSDN:

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.

Example:

DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),'dslfdkjLK85kldhnv$n000#knf');
SELECT HashBytes('SHA1', @HashThis);
GO

answered Jul 28, 2011 at 9:15

sll

62.7k22 gold badges109 silver badges157 bronze badges

Collectives™ on Stack Overflow

How to Prevent Checksum() from generating Duplicate Values in sql server

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related