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

An in-depth look at change detection in SQL Server - Part 02 Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 12:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, May 11, 2015 11:51 PM
Points: 1,521, Visits: 1,898
Comments posted to this topic are about the item An in-depth look at change detection in SQL Server - Part 02

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #1421448
Posted Tuesday, February 19, 2013 4:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 11, 2015 4:23 AM
Points: 3, Visits: 122
About HASHBYTES (from http://msdn.microsoft.com/en-us/library/ms174415.aspx):

1. input size is limited to 8000
2. output size depends on used algorithm, for MD5 you can use CAST(HASHBYTES('MD5', (EmployeeName + CityName)) AS BINARY(16))
3. beware addition NULL values
Post #1421554
Posted Tuesday, February 19, 2013 4:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 24, 2014 4:17 AM
Points: 3, Visits: 45
While the return type of hashbytes might be 8000 bytes, that's more for future proofing, the actual value length will be:
Allowed input values are limited to 8000 bytes. The output conforms to the algorithm standard: 128 bits (16 bytes) for MD2, MD4, and MD5; 160 bits (20 bytes) for SHA and SHA1; 256 bits (32 bytes) for SHA2_256, and 512 bits (64 bytes) for SHA2_512.

from http://msdn.microsoft.com/en-us/library/ms174415.aspx

so your absolute longest using one of the currently supported hashes, is going to be 64 bytes, so your actual storage should be 66 bytes. And you could save those two bytes by doing a cast to a fixed length binary since you will know what algorithm you will be using.
Post #1421555
Posted Thursday, February 21, 2013 1:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 1:51 AM
Points: 9, Visits: 36
Hi

It is dangerous to use hashbytes this way:

 DataHashBytes AS HASHBYTES('MD5', (EmployeeName + CityName))

When EmployeeName = '' and CityName = 'Paris' and someone changes it to EmployeeName = 'Paris' and CityName = '' the hash stays the same. The solution (not very elegant but still a solution) is to use a delimiter, e.g.:

 DataHashBytes AS HASHBYTES('MD5', (EmployeeName + char(0) + CityName))

The article is very useful, thank you.
Post #1422426
Posted Tuesday, April 28, 2015 6:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 11, 2015 1:09 PM
Points: 7, Visits: 34
I read this excellent article and have to confess there is one case which I don't fully comprehend.

"BINARY_CHECKSUM fails to detect symmetric changes"

What is a symmetric change?

What other strings could be used in your example?

thx
Post #1680983
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse