storing foreign language

I would like to store a mix of english and hebrew in a column.  I declared a
column as nvarchar, which is supposed to be able to store unicode, however
all hebrew letters are stored as question marks. I then tried to set the
collate of the column to hebrew_bin, but that did not help. I then changed
the database collate to hebrew_bin, and that worked, but the database became
case-sensitive. I don't understand why nvarchar can't store hebrew without
changing the collate, I thought collate was just for sorting and comparisons
.
Also why didn't the column-level collate help?

create database test
go
use test
go
create table t (s nvarchar(100) collate hebrew_bin)
insert t (s) values('םולש')

select * from t

go

create database test2 collate hebrew_bin
go
use test2
go
create table t2 (s nvarchar(100) )
insert t2 (s) values('םולש')

select * from t2



--
mg

I realized that inserting the data prefixed with N works (see example below)
-
that tells sql server that it is unicode data (you don't need the collate
anymore). Is this the best way to do it? All inserts have to be prefixed wit
h
N? Or is there a global setting that forces it to use unicode?

create database test
go
use test
go
create table t (s nvarchar(100))
insert t (s) values(N'םולש')

select * from t

--
mg


"mikegellis" wrote:

> I would like to store a mix of english and hebrew in a column.  I declared
 a
> column as nvarchar, which is supposed to be able to store unicode, however
> all hebrew letters are stored as question marks. I then tried to set the
> collate of the column to hebrew_bin, but that did not help. I then changed
> the database collate to hebrew_bin, and that worked, but the database beca
me
> case-sensitive. I don't understand why nvarchar can't store hebrew without
> changing the collate, I thought collate was just for sorting and compariso
ns.
> Also why didn't the column-level collate help?
>
> create database test
> go
> use test
> go
> create table t (s nvarchar(100) collate hebrew_bin)
> insert t (s) values('םולש')
>
> select * from t
>
> go
>
> create database test2 collate hebrew_bin
> go
> use test2
> go
> create table t2 (s nvarchar(100) )
> insert t2 (s) values('םולש')
>
> select * from t2
>
>
>
> --
> mg