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.
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.
My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.
My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.
Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports