locked
Need to design the database design to support Chinese language RRS feed

  • Question

  • Hi,

      can anybody give me suggusstion to redesign the SQL Server 2008 R2 database with chinese language support for existing database with varchar datatype (Non- Unicode Data Type)

    Thanks

    Pari

     

     

         

    Thursday, February 19, 2015 6:43 AM

Answers

  • You have to use NVarchar. Here is the script. During Select also you need N in the string

    set nocount on
    go
    USE master;
     GO
     IF DB_ID (N'MyOptionsTest') IS NOT NULL
    DROP DATABASE MyOptionsTest;
     GO
     CREATE DATABASE MyOptionsTest 
     COLLATE Chinese_Taiwan_Stroke_CI_AS;
     GO
     use MyOptionsTest
     go
     IF OBJECT_ID('jj1') IS NULL
     begin
    create table jj1 (name nvarchar(45))
    end
    go
    
    go
     insert into jj1 
     select N'我家己人有淡薄無爽快。'
    
    insert into jj1 
     select N's省城话; t省城話'
    
    insert into jj1
     select N'微软数据库'
    
    insert into jj1
     select N'你叫什么名字?'
    
    insert into jj1
     select N'歡迎!/ 不用謝!/ 不客氣!'
    
    insert into jj1
     select N'我想18號坐飛機到倫敦'
    
    select * from jj1 


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Saturday, February 21, 2015 10:08 AM

All replies

  • chinese language support for existing database with varchar datatype (Non- Unicode Data Type)

    Hello Pari,

    The chinese alphabet do have thousand of characters (http://en.wikipedia.org/wiki/Chinese_character_classification), ASCII can have only 255; so how should that fit? Sorry, no way.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Kalman Toth Thursday, February 19, 2015 8:10 AM
    Thursday, February 19, 2015 7:08 AM
    Answerer
  • Hi,

    You can ensure you're using NVARCHAR/NCHAR to the columns, and make sure Chinese are properly stored into the tables. Read

    Question


    Many Thanks & Best Regards, Hua Min

    • Proposed as answer by Kalman Toth Thursday, February 19, 2015 8:10 AM
    Thursday, February 19, 2015 7:36 AM
  • Thursday, February 19, 2015 8:12 AM
  • Also need to install Asia language on the computer.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, February 19, 2015 8:29 AM
  • i have try with varchar datatype it is supporting. 

    step 1 : create a database with chinese collation

    step 2 : Create a table in that database with varchar datatype

    step 3 : insert with sample chinese data.

    USE master;
    GO
    IF DB_ID (N'MyOptionsTest') IS NOT NULL
    DROP DATABASE MyOptionsTest;
    GO
    CREATE DATABASE MyOptionsTest 
    COLLATE Chinese_Taiwan_Stroke_CI_AS;
    GO

    IF OBJECT_ID('jj') IS NOT NULL
    create table jj (name varchar(45))

    insert into jj1 
    select '我家己人有淡薄無爽快。'

    insert into jj1 
    select 's省城话; t省城話'

    insert into jj 
    select '微软数据库'

    insert into jj
    select '你叫什么名字?'

    insert into jj
    select '歡迎!/ 不用謝!/ 不客氣!'

    insert into jj
    select '我想18號坐飛機到倫敦'

    select * from jj

    whether it will work properly?

    Friday, February 20, 2015 10:20 AM
  • Why don't you test it on your own? I works ... for some characters, but not for all, the will be replace by a question mark ? =>


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, February 20, 2015 1:06 PM
    Answerer
  • You have to use NVarchar. Here is the script. During Select also you need N in the string

    set nocount on
    go
    USE master;
     GO
     IF DB_ID (N'MyOptionsTest') IS NOT NULL
    DROP DATABASE MyOptionsTest;
     GO
     CREATE DATABASE MyOptionsTest 
     COLLATE Chinese_Taiwan_Stroke_CI_AS;
     GO
     use MyOptionsTest
     go
     IF OBJECT_ID('jj1') IS NULL
     begin
    create table jj1 (name nvarchar(45))
    end
    go
    
    go
     insert into jj1 
     select N'我家己人有淡薄無爽快。'
    
    insert into jj1 
     select N's省城话; t省城話'
    
    insert into jj1
     select N'微软数据库'
    
    insert into jj1
     select N'你叫什么名字?'
    
    insert into jj1
     select N'歡迎!/ 不用謝!/ 不客氣!'
    
    insert into jj1
     select N'我想18號坐飛機到倫敦'
    
    select * from jj1 


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Saturday, February 21, 2015 10:08 AM