Why do prefixing string with N give a different code

Question

My question is based on the observation of the results of the following queries ran against SQL Server 2014.

select convert(varbinary(200), 'A')

-------------------------------------
0x41

select convert(varbinary(200), N'A')

-------------------------------------
0x4100

select convert(varbinary(200), 'ஆ')

-------------------------------------
0x3F

select convert(varbinary(200), N'ஆ')

-------------------------------------
0x860B

The character 'ஆ' was copied from the internet.

Why is the code of 'ஆ' different when queried with N prefix?

Is this because the SQL Server by default uses a code page that has code point 0x3F for 'ஆ'?

The queries were run in SSMS on my box (different from the sql box). How did SSMS manage to communicate the difference between 'ஆ' and N'ஆ' to SQL Server?

EDIT:

Are texts WITHOUT N prefix considered non-unicode and text with N prefix considered unicode? If so why does the statement select '晥' below return '?'s?

The unicode code point of '晥' is 0x6566 (as seen below)

select convert(varbinary(10), N'晥')
----------------------
0x6566

Now when interpreting that code point as varchar (non-unicode) I get 'ef' (as 65 and 66 are the code points for those chars in the default code page).

declare @c varbinary(10) = 0x6566
select convert(varchar(10), @c)
----------
ef

But why do I get '?' when doing this? (shouldn't it also have been interpreted as varchars as N prefix is missing?

select '晥'
----
?

EDIT:

I am aware that the '?'s are returned as a result of SQL server not being able to map the unicode code point 0x6566 to a code point on the default code page. But how did it know from the statement select '晥' that the text stream within quotes had to be interpreted as unicode despite the missing N?

`3F` is `?` - it *couldn't* map the character into the code page so it used the replacement character — Damien_The_Unbeliever, Feb 27 '18 at 13:35
The `N` prefix indicates a **Unicode** string literal, while otherwise, it's a **non-Unicode** string literal. — marc_s, Feb 27 '18 at 13:35
@Damien_The_Unbeliever, sorry, I am struggling to follow.. what couldn't map the character into what code page and what replacement character was used? — Social Developer, Feb 27 '18 at 13:46
@marc_s was it SSMS that consumed the N prefix then, to communicate something different to SQL server? — Social Developer, Feb 27 '18 at 13:47
@Damien_The_Unbeliever, I now get it. See EDITed question above. Thanks — Social Developer, Mar 07 '18 at 11:56

Why do prefixing string with N give a different code

0 Answers0