Click here to Skip to main content
15,881,027 members
Articles / Programming Languages / SQL

Strange Behaviour Converting NVARCHAR(MAX) to BINARY

Rate me:
Please Sign up or sign in to vote.
3.46/5 (4 votes)
10 Mar 2018CPOL 5K   3
Strange behaviour converting NVARCHAR(MAX) to BINARY

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.

SQL
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.

SQL
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

image_thumb1

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
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

Comments and Discussions

 
SuggestionSome explanation Pin
Manjuke Fernando17-Mar-18 3:13
professionalManjuke Fernando17-Mar-18 3:13 
I got this reply on LinkedIn by Ron Dunn (Cloud Data Warehouse Advisor)


Quote:
I got some feedback on this behaviour. It is by design, the same pattern would occur if you were using fixed length NCHAR strings. Unicode strings use the EN QUAD character instead of the EN SPACE. I also got the following advice: "converting Unicode to binary is rife with peril". Coupled with my previous comment about non-guaranteed conversions between versions, I strongly recommend you find an alternate strategy.

QuestionDid you see ... Pin
Richard MacCutchan10-Mar-18 22:36
mveRichard MacCutchan10-Mar-18 22:36 
AnswerRe: Did you see ... Pin
the Kris12-Mar-18 22:59
the Kris12-Mar-18 22:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.