1a:T712,

Few days back, I was writing a CLR function to be used for hashing string values. The only option was the CLR functions since T-SQL doesn’t have any functionality to convert a string to hashed value using a key. Using the HASHBYTES function, you can only provide the algorithm.

DECLARE @Data NVARCHAR(4000);  
SET @Data = CONVERT(NVARCHAR(4000),'My Secret Message');  
SELECT HASHBYTES('SHA1', @Data);

I have written the CLR function to achieve the requirement, but during testing, the validation was failing and when I go through the code, I couldn’t find any issue in the function as well. But inspecting carefully, I noticed that when a variable type NVARCHAR(n) and a variable type of NVARCHAR(MAX) gives different results when it’s converted to Binary. Which was the root cause for the issue I was facing.

DECLARE
    @Data1  AS NVARCHAR(MAX) = '1111'
    ,@Data2 AS NVARCHAR(10) = '1111'
 
SELECT
    CAST(@Data1 AS BINARY(30)) AS ValueMax
SELECT
    CAST(@Data2 AS BINARY(30)) AS ValueN

As you can see in the above example, the zero bytes are represented differently for NVARCHAR(MAX) when it’s converted to BINARY.

I do not have any explanation for this. I am sharing the information in case anyone comes across this issue. Please feel free to comment.

Strange Behaviour Converting NVARCHAR(MAX) to BINARY