[SOLVED] Eastern Character Set Causes Problems For SQL Server 2012

Eastern Character Set Causes Problems For SQL Server 2012

I have a table with contents:

internalid  foreignWord
1   បរិស្ថាន
2   ការអភិវឌ្ឍសហគមន៍

And its schema:

CREATE TABLE [dbo].[CE_testTable](
    [internalid] [int] IDENTITY(1,1) NOT NULL,
    [foreignWord] [nvarchar](50) NOT NULL

If I run:

SELECT TOP 1000 [internalid]  ,[foreignWord] FROM CE_testTable where foreignWord = N'ការអភិវឌ្ឍសហគមន៍'

I get:

internalid  foreignWord
1   បរិស្ថាន
2   ការអភិវឌ្ឍសហគមន៍

Which is both rows, it should have only returned the row with "ការអភិវឌ្ឍសហគមន៍" which is "community development" in Cambodian.

It is a NVARCHAR column and I'm selecting where N' etc? Any ideas?

Solution

Change the collation to Latin1_General_100_CI_AS.

You can specify collation for each column when you create the tables.

If you don't specify collation the columns will have the same collation that the database has.

CREATE TABLE [dbo].[CE_testTable](
    [internalid] [int] IDENTITY(1,1) NOT NULL,
    [foreignWord] [nvarchar](50) collate Latin1_General_100_CI_AS NOT NULL
)

SQL Fiddle