CODEWITHSUNDEEP
sql-serverhash
JavaSheriff
JavaSheriff

Reputation: 7665

Calculate MD5 for a long string

When calling HASHBYTES with long string I am getting 

Msg 8152, Level 16, State 10, Line 11
String or binary data would be truncated.

I am trying to calculate the MD5 hash for multiple fields together so I can compare objects,
Is there anyway around this?

Upvotes: 1

Views: 1417

Answers (2)

Martin Carlsson
Martin Carlsson

Reputation: 471

As @TimBiegeleisen said. SQL Server has an 8k bytes limitation on HASHBYTES.

However, it looks like that SQL Server 2016 and forward don't have this limitation.

For SQL Server 2014 (12.x) and earlier, allowed input values are limited to 8000 bytes. https://learn.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-2017

Upvotes: 1

beercohol
beercohol

Reputation: 2587

Assuming you're using SQL Server 2008 or above, use the CHECKSUM function.

https://msdn.microsoft.com/en-us/library/ms189788.aspx

CHECKSUM computes a hash value, called the checksum, over its list of arguments. The hash value is intended for use in building hash indexes. If the arguments to CHECKSUM are columns, and an index is built over the computed CHECKSUM value, the result is a hash index. This can be used for equality searches over the columns.

CHECKSUM returns an error if any column is of noncomparable data type. Noncomparable data types are text, ntext, image, XML, and cursor, and also sql_variant with any one of the preceding types as its base type.

Upvotes: 1

Related Questions