Sqlserver database Collation is not working

Question

Version:

Microsoft SQL Server 2014 - 12.0.4100.1 (X64) 
Apr 20 2015 17:29:27 
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Server Collation:

SQL_Latin1_General_CP1_CI_AS

Database(name: test) Collation(I modified it from SQL_Latin1_General_CP1_CI_AS to Japanese_CI_AS)

Japanese_CI_AS

Test Table(assume just one column with table name t_test):

name varchar(255)

My Question:

Before I modified the test db Collation(now it is SQL_Latin1_General_CP1_CI_AS ), if i execute below sql:

insert into t_test(name) values('り');

the result is that name saved as '?', i understood this behaver.

After I modified the test db Collation(now it is Japanese_CI_AS), if i execute below sql:

insert into t_test(name) values('り');

the result is that name still saved as '?', i don't know why?

somebody know? i must use varchar type, because this is thirdparty database, i can't modify it. how to fix it?

Does the db Collation must as the same with Server Collation ?

When Server Collation is Japanese_CI_AS, i can insert any japanese successful.

Case 1:

1. select serverproperty('collation');

    SQL_Latin1_General_CP1_CI_AS

2. use master;
3. select databasepropertyex('test_db','collation');

    SQL_Latin1_General_CP1_CI_AS

4. use test_db;
5. CREATE TABLE t_test(name VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS);
insert into t_test(name) values('り');
insert into t_test(name) values(N'り');
SELECT * FROM t_test;
DROP table t_test;

    ?
    ?

Case 2:
1. select serverproperty('collation');

    SQL_Latin1_General_CP1_CI_AS

2. use master;
3. select databasepropertyex('test_db','collation');

    SQL_Latin1_General_CP1_CI_AS

4. use test_db
4. CREATE TABLE t_test(name VARCHAR(128) COLLATE Japanese_CI_AS);
insert into t_test(name) values('り');
insert into t_test(name) values(N'り');
SELECT * FROM t_test;
DROP table t_test;

    ?
    り

Case 3:
1. select serverproperty('collation');

    SQL_Latin1_General_CP1_CI_AS

2. use master;
2. alter  database  test_db collate Japanese_CI_AS;
3. select databasepropertyex('test_db','collation');

    SQL_Latin1_General_CP1_CI_AS

4. use test_db;
5. select databasepropertyex('test_db','collation');

    Japanese_CI_AS

6. CREATE TABLE t_test(name VARCHAR(128) COLLATE Japanese_CI_AS);
insert into t_test(name) values('り');
insert into t_test(name) values(N'り');
SELECT * FROM t_test;
DROP table t_test;

    り
    り

how do above three cases work?

You need to use `NVARCHAR` type and insert statements like `insert into t_test(name) values(N'り');` (the N is for wide-character strings). — TT., Feb 25 '16 at 08:59
`You do know that collation is a property of each column?` The database collation is just default for new columns. (i.e. to change collation in an existing database you must change collation on each column) — adrianm, Feb 25 '16 at 11:38
@adrianm yes, i got it. now, do you know how the CASE 2 work? — Tony, Feb 25 '16 at 13:01

Tony · Accepted Answer · 2016-02-29T07:04:47.870

Finally, i find the answer:

If you create a table with a text column that has a different code page than the code page of the database's default collation, there are only two ways you can specify data values to be inserted into the column, or update existing values. You can:

Specify a Unicode constant.
Select a value from another column with the same code page.

https://technet.microsoft.com/en-us/library/aa214897%28v=sql.80%29.aspx

Sqlserver database Collation is not working

1 Answers1