CODEWITHSUNDEEP
sql-server
Rohi_Dev_1.0
Rohi_Dev_1.0

Reputation: 386

Using varchar(8000) column value in HashByte md5 function

I want to create a hash using all the row values in SQL. In that table one of the column length is varchar(8000).I put a hashbyte function like below -

Hashbyte('MD5',column1+column2+....) -- column1 having varchar(8000) length and it contains string of length 8000.

Then it gives me same hashbyte where the rows having same value in column1 even if other columns contains different data

Then I converted column1 to varchar(max) in hashbyte function, I got different hashbyte for each row.

Hashbyte('MD5',convert(varchar(max),column1)+column2+....)

Why the hashbyte('MD5'...) wont take all column values?

If you want to try one more example of having varchar(8000) column issue- try to calculate the length

create a table having column with varchar(8000) and calculate length of all column values. It will give you 8000 only. Next convert the varchar(8000) to varchar(max) it will give you correct result.

len(column1+column2...) --> 8000
len(convert(varchar(max),column1)+column2...) --> actual length

adding any string with varchar(8000) is such an issue?

Upvotes: 0

Views: 147

Answers (1)

Thom A
Thom A

Reputation: 95534

You're under the misconception that a varchar(8000) concatenated to a varchar(8000) (or even any other length <= 8000) results in a varchar(MAX). This is not true. To get a MAX length you must define at least one of the values in the expression as a MAX.

This is confirmed in the remarks in + (String Concatenation) (Transact-SQL):

Remarks

...

If the result of the concatenation of strings exceeds the limit of 8,000 bytes, the result is truncated. However, if at least one of the strings concatenated is a large value type, truncation does not occur.

As a result you need to convert one of the values first to MAX and then the rest would implicitly be cast to a MAX as well. If you don't explicitly convert (at least) one of the expressions, then the value will be truncated, as the documentation states.

Obviously this applies to nvarchar as well, where truncation occurs at 4,000 characters (which is still 8,000 bytes).

Upvotes: 1

Related Questions