Why can I store an Ukrainian string in a varchar column?

Question

I got a little surprised as I was able to store an Ukrainian string in a varchar column .

My table is:

create table delete_collation
(
   text1 varchar(100) collate SQL_Ukrainian_CP1251_CI_AS
)

and using this query I am able to insert:

insert into delete_collation
values(N'використовується для вирішення квитки')

but when I am removing 'N' it is showing ?????? in the select statement.

Is it okay or am I missing something in understanding unicode and non-unicode with collate?

i am going to use unicode only.just want to clear some doubts regarding DBCS and unicode. — gkarya42, Sep 09 '15 at 08:58
if you're in doubt, read [this](http://www.joelonsoftware.com/articles/Unicode.html) — phuclv, Sep 09 '15 at 11:41

score 4 · Accepted Answer · edited May 23 '17 at 11:58

4

From MSDN:

Prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.

UPDATE:
Please see a similar questions::
What is the meaning of the prefix N in T-SQL statements?
Cyrillic symbols in SQL code are not correctly after insert
sql server 2012 express do not understand Russian letters

edited May 23 '17 at 11:58

Community

1
1

answered Sep 09 '15 at 07:30

Roman Marusyk

23,328
24
73
116

what you mean by default code page.is it code page defined in my collate of column or defined for the daabase – gkarya42 Sep 09 '15 at 08:55
for the daabase: `SELECT SERVERPROPERTY('Collation')` – Roman Marusyk Sep 09 '15 at 09:18

score 4 · Answer 2 · answered Sep 09 '15 at 07:34

To expand on MegaTron's answer:

Using collate SQL_Ukrainian_CP1251_CI_AS, SQL server is able to store ukrainian characters in a varchar column by using CodePage 1251.

However, when you specify a string without the N prefix, that string will be converted to the default non-unicode codepage before it is sent to the database, and that is why you see ??????.

So it is completely fine to use varchar and collate as you do, but you must always include the N prefix when sending strings to the database, to avoid the intermediate conversion to default (non-ukrainian) codepage.

Why can I store an Ukrainian string in a varchar column?

2 Answers2