InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 16:36:55 GMT20http://www.sqlservercentral.com/Forums/Topic1421448-2785-1.aspxHiIt is dangerous to use hashbytes this way:[code="sql"] DataHashBytes AS HASHBYTES('MD5', (EmployeeName + CityName))[/code]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.:[code="sql"] DataHashBytes AS HASHBYTES('MD5', (EmployeeName + char(0) + CityName))[/code]The article is very useful, thank you.Thu, 21 Feb 2013 01:32:03 GMTTarantilohttp://www.sqlservercentral.com/Forums/Topic1421448-2785-1.aspxWhile the return type of hashbytes might be 8000 bytes, that's more for future proofing, the actual value length will be:[quote]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.[/quote]from http://msdn.microsoft.com/en-us/library/ms174415.aspxso 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.Tue, 19 Feb 2013 04:42:34 GMTpalesius 61659http://www.sqlservercentral.com/Forums/Topic1421448-2785-1.aspxAbout HASHBYTES (from http://msdn.microsoft.com/en-us/library/ms174415.aspx):1. input size is limited to 80002. output size depends on used algorithm, for MD5 you can use CAST(HASHBYTES('MD5', (EmployeeName + CityName)) AS BINARY(16))3. beware addition NULL valuesTue, 19 Feb 2013 04:42:31 GMTMr.DiGihttp://www.sqlservercentral.com/Forums/Topic1421448-2785-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/SQL+Server/95931/">An in-depth look at change detection in SQL Server - Part 02</A>[/B]Tue, 19 Feb 2013 00:01:37 GMTNakul Vachhrajani