SQL Server Performance

How to convert data into Unicode

Discussion in 'General DBA Questions' started by Mast_dba, May 16, 2006.

  1. Mast_dba New Member

    Hi Friend,

    Currently my server store data into non-unicode format but now we need to store data into unicode format. Can anyone please tell me what property should I change at database level so that it can store data into unicode format. I can rebuild the database. so please tell me how can i set the required property.

    Thanks a lot for your help.


    MAST
    ITS IMPOSSIBLE TO DEFEAT A PERSON WHO NEVER GIVE UP.
  2. Twan New Member

    Hi ya,

    going from non-unicode to unicode is 'relatively' painless. You can change the datatype of all relevant columns from varchar or char to nvarchar or nchar. Before you start this ensure that none of your tables are close to sql's 8000 byte limit for a single row, as unicode implies two bytes per character so a char(1000) takes 1000 bytes, but and nchar(1000) takes 2000 bytes

    Care must be taken that you also change all appropriate trigger/stored procedure parameters and variables, and of course the program code

    obviously you do NOT want to do this on a production server...

    Cheers
    Twan
  3. Mast_dba New Member

    Thanks Twan.

    Do u have any idea which collation should I choose for binary sort. Please reply ASAP because I m going to create database.

    Thanks


    MAST
    ITS IMPOSSIBLE TO DEFEAT A PERSON WHO NEVER GIVE UP.
  4. derrickleggett New Member

    Why are you using binary collation? If you are going to use it, you can just choose the SQL_Latin1_BIN, which is the default binary sort order/collation (if I remember right). I'm at an old shop where they have some CP437_BIN left over from the pre-7.0 days. I really don't see a reason to use binary at all though, so I'm curious why you are pursuing this route.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  5. satya Moderator

    1. Set the codepage to 65001 for any pages that contain data retrieved from the server. This will automatically convert any UCS-2/UTF-16 data to UTF-8 format when the page is rendered and sent to the client.

    2. Also set the codepage to 65001 for any data sent from a Web page, since the Web server will also automatically convert UTF-8 data sent to SQL Server into UCS-2.

    Note that a database won't explicitly support Unicode data unless the field types in question are also explicitly supported.


    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. Mast_dba New Member

    Hi Satya,

    Could you please explain this in little more detail ? Actaully I dont have any exprience with setting pagecode. Could you please guide me how set codepage & if I set codepage to 65001 then still do I need to use nchar & nvarchar or char/varchar datatype works for unicode.

    Thanks a lot for ur help.


    MAST
    ITS IMPOSSIBLE TO DEFEAT A PERSON WHO NEVER GIVE UP.
  7. satya Moderator

    http://support.microsoft.com/?kbid=232580

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. Mast_dba New Member

    Thanks dude for information.

    MAST
    ITS IMPOSSIBLE TO DEFEAT A PERSON WHO NEVER GIVE UP.

Share This Page