Search code examples
sql-servercharacter-encodingsql-server-2008-express

SQL Server character set and N prefix

[THIS IS NOT A QUESTION ABOUT NVARCHAR OR HOW TO STORE CHINESE CHARACTER]

SQL Server 2008 Express

Database collation is SQL_Latin1_General_CP1_CI_AS

create table sample1(val varchar(2))
insert into sample1 values(N'中文')
  1. I know these Chinese characters would become junk characters.
  2. I know I can use nvarchar to overcome all problem.

What I don't know is: why there isn't "string too long" error when I run the insert statement?

  1. N prefix means that client will encode the string using UNICODE.
  2. 2 Chinese characters will become 4 bytes.
  3. varchar(2) can only contain 2 bytes.

Why people down vote this question? really?

Solution

  • More explanation to @marc_s answer.

    The character N'中文' will be converted to varchar with the collation SQL_Latin1_General_CP1_CI_AS. Since there is no such character in the code page, it will converted to not defined, and 0x3f3f in the end. 0x3f is the question mark, so there will be two question marks in this case and it won't exceed the column length.