Jim Youmans-439383 (11/25/2013)
The Hashbytes function should always return a varbinary(20), but the input values are limited to 8000 bytes. So if your row adds up to more than 8000 bytes, then that would be the issue. Instead of doing a "SELECT *" you could do a SELECT of only the columns that you want to watch for change on.Jim
Agreed, Jim - particularly with the XML PATH, there's a huge amount of overhead for each row, so it's very easy to end up with truncation/errors.
Additionally, where are the benchmarks (done with Profiler) that show performance differences, including the overhead of the extra column and HASHBYTES calculations, with good indexing?
Many other pitfalls involved in using hashes to detect differences are discussed in:
http://www.sqlservercentral.com/Forums/Topic902868-507-1.aspx
Suffice it to say:
If the hash is different, the data is different.
If the hash is the same, you know nothing about the data for certain.