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.