When executing that query:

SELECT HashBytes('MD5', CONCAT('A',convert(nvarchar,100),'1234')) as mycol ...

I get HashBytes represented such as

0xA6A0D....

When I do not convert, the HashBytes look like the following:

SELECT HashBytes('MD5', CONCAT('A','100','1234')) as mycol

0x88230...

Why is it different?

CodePudding user response：

As mentioned by Larnu in the comments, data types matter. To see why you're getting different hashes lets take a look at what CONCAT outputs for the two cases:

SELECT CAST(CONCAT('AVS',convert(nvarchar,313),'@310001') AS VARBINARY(MAX));

SELECT CAST(CONCAT('AVS','313','@310001') AS VARBINARY(MAX));

Because one of the parameters to CONCAT is of type nvarchar the others are coerced to nvarchar as well to return an nvarchar result.

Read through the Remarks section of the CONCAT (Transact-SQL) documentation for a complete accounting of the expected output types for given input types.