Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using the CheckSum Function - SQL School Video Expand / Collapse
Author
Message
Posted Tuesday, November 25, 2008 11:59 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, June 4, 2014 12:29 PM
Points: 1,931, Visits: 234
Comments posted to this topic are about the item Using the CheckSum Function - SQL School Video

Brian Knight
Free SQL Server Training Webinars
Post #608873
Posted Wednesday, December 17, 2008 4:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 15, 2014 7:08 PM
Points: 40, Visits: 243
this is a good/ dangerous technique. I have implemented this is a production system and got burned by duplicate checksum values on different columns (hash collision). Them more data you can put into the check sum the less likely you will have duplicates. maybe.

But good demo.

Post #621718
Posted Wednesday, December 17, 2008 6:48 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, June 4, 2014 12:29 PM
Points: 1,931, Visits: 234
That's true. There's a slight chance of duplicate checksums. Another technique that I've used is HASHBYTES similar to this:


select HASHBYTES('md5',NAME + isnull(Color,'Unkown')), * FROM Production.Product

It takes longer to run but it produces very unique values like 0x313FB214C93591081E720123253B1398.


Brian Knight
Free SQL Server Training Webinars
Post #621733
Posted Thursday, December 18, 2008 12:34 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:15 AM
Points: 5,471, Visits: 1,402
Nice one...


Post #621823
Posted Thursday, December 18, 2008 10:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 5, 2011 11:42 AM
Points: 3, Visits: 117
Brian,
That's an excellent tip!
Wee're in the middle of a conversion project for HR & FIN and this will absolutely help.
Great Job!
Post #622309
Posted Friday, December 19, 2008 1:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 19, 2010 8:44 AM
Points: 1, Visits: 6
you should idd use MD5!


using checksum results easily in duplicates , eg
select checksum('eeeeeeeeeeeeeeee')
select checksum('dddddddddddddddd')
Post #622667
Posted Friday, December 19, 2008 8:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:53 AM
Points: 23, Visits: 119
Agreed, having worked on a system where someone decided to implement checksums to manage change control I know from bitter experience that it will bite you at some point. I think we found numbers that were related in some mathematical way (I forget exactly) would give the same value. But we also had examples of company name strings that gave the same value.

If getting the integrity perfect is important I'd suggest not using this idea. For something that is supposed to be a rare occurrence it happened surprisingly frequently!
Post #623027
Posted Friday, December 19, 2008 11:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 21, 2014 6:47 AM
Points: 41, Visits: 70
When implementing the HashBytes or CheckSum is it optimal to do these checks manually in the respective update stored procedure, or is it better to be used trigger based? What is the best practice?

My goal is to provide auditing (stored in a separate audit table) in sql server 2005 when a specific piece of data e.g. username or users password is modified, when the enter user row is updated.

Post #623204
Posted Sunday, December 21, 2008 2:45 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 542, Visits: 802
First of all kudos to Brian for his videos. I absolutely love them and have learned so much. Secondly I agree with the others regarding the dangers of using CHECKSUM against string values due to the non-uniqueness of the result. I think it's fairly safe to use across several numeric/date columns though. I have never used HASHBYTES before, so this is a great tip as well.

Thank you!



Post #623656
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse