none
Unicode required to support English and Canadian French?

    Question

  • Hi all,

     

    Simple question hopefully. We are installing an app that requires Canadian English and Canadian French language support. When installing SQL server:

    1) Do we require UNICODE?

    2) What collation should we specify for the SQL server to support both languages in our database?

    3) If our SQL server has been installed withdefault settings/collation, if we create the db in (whatever collation you specify in #2 above), will there be any behavioural problems for sorting, whatever?

     

    Thanks, i guess i may not understand the use of unicode/collations that well...yet.

     

    Saturday, September 03, 2011 10:59 AM

Answers

  • Hello,

    Choose Latin1_General as collation, since it supports all western European Languages.

    Use Unicode data types.

    http://msdn.microsoft.com/en-us/library/ms186939.aspx

    http://support.microsoft.com/kb/239530

    If you install the SQL Server instance with a collation incompatible with the collation of a specific database, for instance, you may have collation conflicts. 

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Proposed as answer by Peja Tao Tuesday, September 06, 2011 3:12 AM
    • Marked as answer by Peja Tao Friday, September 09, 2011 9:01 AM
    Saturday, September 03, 2011 2:26 PM
    Moderator
  • give you some  more infomation.

    ASCII character can be stored in SQL Server as varchar or char data type which will take one storage byte per character.

    You can store non ASCII characters using either

    a) creating a column of character data type (either varchar or char) with appropriate collation for e.g. if you create column with Arabic collation you can store arabic character in varchar columns and it still takes only one byte per character.

    B) or You specify your column with data type nvarchar or nchar where N stands for national character and it will allow you to store any character/language that unicode supports. This however increase storage overhead as each character will take two bytes.  

     

    >>> Just wondering how/where i can specify the unicode piece?

    create table t1
    (
      keycol int identity(1,1) primary key,
      data 	 nvarchar(30) NOT  NULL
    
    
    )

     

    By specifying column with data type of nvarchar or nchar

    for more info

    http://msdn.microsoft.com/en-us/library/ms180059.aspx


    • Edited by Chirag Shah Tuesday, September 06, 2011 12:51 AM
    • Proposed as answer by Peja Tao Tuesday, September 06, 2011 3:16 AM
    • Marked as answer by Peja Tao Friday, September 09, 2011 9:01 AM
    Tuesday, September 06, 2011 12:50 AM
  • Hi dnaman,

    Yes,please install SQL server with a collation " Latin1_General" and create the character columns as nchar/nvarchar type.

     

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Peja Tao Friday, September 09, 2011 9:01 AM
    Tuesday, September 06, 2011 3:19 AM

All replies

  • Hello,

    Choose Latin1_General as collation, since it supports all western European Languages.

    Use Unicode data types.

    http://msdn.microsoft.com/en-us/library/ms186939.aspx

    http://support.microsoft.com/kb/239530

    If you install the SQL Server instance with a collation incompatible with the collation of a specific database, for instance, you may have collation conflicts. 

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Proposed as answer by Peja Tao Tuesday, September 06, 2011 3:12 AM
    • Marked as answer by Peja Tao Friday, September 09, 2011 9:01 AM
    Saturday, September 03, 2011 2:26 PM
    Moderator
  • Thanks Albert - one more question. Do i explicitly select UNICODE as a data type or, by selecting latin1_General as my collation, it will support unicode datatypes? Just wondering how/where i can specify the unicode piece?

     

    Regards,

    Mark

     

    Tuesday, September 06, 2011 12:02 AM
  • Hello,

    No you need to explicitly select Unicode datatypes. Instead of varchar, for example, use nvarchar. The n stands for Unicode.

    Use Unicode data types on the definition of columns for tables in your database.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Tuesday, September 06, 2011 12:45 AM
    Moderator
  • give you some  more infomation.

    ASCII character can be stored in SQL Server as varchar or char data type which will take one storage byte per character.

    You can store non ASCII characters using either

    a) creating a column of character data type (either varchar or char) with appropriate collation for e.g. if you create column with Arabic collation you can store arabic character in varchar columns and it still takes only one byte per character.

    B) or You specify your column with data type nvarchar or nchar where N stands for national character and it will allow you to store any character/language that unicode supports. This however increase storage overhead as each character will take two bytes.  

     

    >>> Just wondering how/where i can specify the unicode piece?

    create table t1
    (
      keycol int identity(1,1) primary key,
      data 	 nvarchar(30) NOT  NULL
    
    
    )

     

    By specifying column with data type of nvarchar or nchar

    for more info

    http://msdn.microsoft.com/en-us/library/ms180059.aspx


    • Edited by Chirag Shah Tuesday, September 06, 2011 12:51 AM
    • Proposed as answer by Peja Tao Tuesday, September 06, 2011 3:16 AM
    • Marked as answer by Peja Tao Friday, September 09, 2011 9:01 AM
    Tuesday, September 06, 2011 12:50 AM
  • Ahh ok so i think i get it now, is this right?

     

    If i DO install SQL server with a collation that supports all European languages, say like Latin1_General, ..

    THEN..when i define my tables, i can use column definition such as nchar, nvarchar, to store the english and canadian french characters.

    If i DONT install SQL server with a collation that supports all European languages (in my case Canadian English), then i can't use nchar, nvarchar to store the language characters?

    Does that make sense? I think im getting it..

    Tuesday, September 06, 2011 2:05 AM
  • Hi dnaman,

    Yes,please install SQL server with a collation " Latin1_General" and create the character columns as nchar/nvarchar type.

     

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Peja Tao Friday, September 09, 2011 9:01 AM
    Tuesday, September 06, 2011 3:19 AM
  • Hi All,

    I have a SQL installation where the server collation is set to 'SQL_Latin1_General_CP1_CS_AS'. I know in the above posts, you mentioned to set the server collation to Latin1_General to support english and french characters.

    Will 'SQL_Latin1_General_CP1_CS_AS' support the French characters? Just that i'm seeing squares where i should be seeing the French characters in my databases now. The columns, however, are set to use nvarchar(xx).

    Thanks,

    Thursday, February 23, 2012 2:29 PM
  •  

    Hello,

    How you are populating that table or those tables?


    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, February 23, 2012 3:13 PM
    Moderator
  •  

    Hello,

    How you are populating that table or those tables?


    Regards,

    Alberto Morillo
    SQLCoffee.com


    We have third-party app, that we use to import a .csv file into the tables. This .csv file has French characters properly shown.
    Thursday, February 23, 2012 3:37 PM
  •  

    Hello,

    If the application uses the ACP codepage, then you will probably see those French characters properly shown.  Maybe that application allows you to specify ACP.

    http://msdn.microsoft.com/en-us/library/ms188365.aspx

    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, February 23, 2012 3:52 PM
    Moderator
  • Hi All,

    To also provide further closure on this, i found out that i had to save the .txt file as encoding UTF-8 and then when i imported it into the DB, it showed the French characters properly....

    Thanks all for the help!

    Monday, March 05, 2012 9:36 PM
  • Thank you for the feedback provided.

    Have a great day!

    Monday, March 05, 2012 10:02 PM
    Moderator
  • Do you really need nvarchar for French?

    Nobody mentions that it is waste of space if not needed.

    If the collation is USA default, you need nvarchar because of the Hungarian UNICODE letters ő and ű .


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Thursday, April 17, 2014 7:19 PM