SQL SERVER – 2005 Collation Explanation and Translation

Just a day before one of our SQL SERVER 2005 needed Case-Sensitive Binary Collation. When we install SQL SERVER 2005 it gives options to select one of the many collation. I says in words like ‘Dictionary order, case-insensitive, uppercase preference’. I was confused for little while as I am used to read collation like ‘SQL_Latin1_General_Pref_Cp1_CI_AS_KI_WI’. I did some research and find following link which explains many of the SQL SERVER 2005 collation.

Complete documentation MSDN – SQL SERVER Collation

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

28 thoughts on “SQL SERVER – 2005 Collation Explanation and Translation

  1. Hi Pinal,

    I have an interesting and strange question for SQL collations.

    Issue: I work for a search engine. I have a column that sores the keywords that users enter for search criteria. Now there are some chinese, japanese or say thai users who enter keywords in their native language. I would like my “Text_keyword” to store data in any language and any fonts so that i would be able to store the information from multiple users across the globe. I am mentioning the other required specifications for you kind perusal.

    column name: Text_keyword
    Datatype: Nvarchar(max)
    Collation name: Japanese_unicode_CI_AS
    I have selected all of the “CODE PAGE CONVERSION TABLES” by going to >> control panel >> Regional and Language options >> Advanced options.

    Sample script that I have stored in my database.

    如果?不需要的一切權力指數調諧嚮導 , 但仍希望協助創建索引的表, ?可以使用”表現指數分析”選項設下的”?詢 “菜單上的?詢分析器SQL Server中 7 . 與 SQL Server 2000 ,選擇 “索引調整嚮導 ” ,從 “?詢 “菜單中的?詢分析器. 而是採用了輪廓跟蹤?案進行了分析,它使用了?詢 ,發現在?詢窗口. 雖然未能全面分析作為一個?從用指數調諧嚮導 , 這是一個很好的出發點時 ,進行了性能分析,具體問題 ,在?的數據庫 . [ 7.0 , 2000年] 2007年3月6日更新

    If u noitce this script there are some question marks (?) in it.
    There are supposed to be some other chinese characters in it which my sql server 2005 is not able to store and thus giving me those ? signs.

    Any reply from your side would be highly appreciated.

    Thanks & Regards,
    Amit Pandey
    DBA SQL Server
    Espire Infolabs Pvt.Ltd

  2. Very interesting question Amit,

    Let me give you my opinion. First I have never worked with Chinese language so I do not sure if that ? is Chinese char or it should be ? itself.

    From my experience with multi-language database I have think following thing may help.
    1) There is no proper collation of the foreign language.

    2) We had problem when our website is searched by someone who knows Italian and Spanish together, we need different collation for both. However, if we use only one we end up with only BOX symbol in our browser.
    If we know our search is in those language we change collation in both the language and merge them using our complex logic matching algorithm/function.

    3) sometime stored data is in special char and searched char when they reach at comparison level they are also compared to special char and search works for us.
    What these means is if “Apple” is converted into database is “bapple”, when we try to search for apple it is converted to “bapple” when collation used in our database. So “bapple” is compared against “bapple” and gives us match.

    4) It may be possible they are query string separate ( this is what I just thought, while writing this comment)

    Kind Regards,
    Pinal Dave (SQLAuthority.com)

  3. Hi Dave,

    I have a problem on save Thai character in any table. I’m using SQL Server 2005 and I’ve changed the collation into THAI_CI_AS. I don’t know what setup else that I miss, every I save by using Thai character, there was always ‘????’ character appear on the field.
    Could you give me some suggestion?

    Thanks,

    Afin

  4. Hi pinal,

    I have a table with some columns where both english and chinese data are stored. when i query the table, the chiese characters appear like barcodes. However, when i copy the particular data and paste it in the query pane or any text editor(MS word , notepad etc), i am able to see the proper chinese text. Can you please explain why this is so?
    Also, when i query the same table with a ‘where’ condition involving that particular column and paste the same chinese text as the condition value, the query doesn’t show up any results. Any help in this regard would be of great help. Thanks.

    P.S: I’m still a newbie to sql server 2005

  5. Hi.

    I was having a boatload of trouble with Chinese characters in SQL Server 2005.

    Found some things that worked.

    If the column is of ‘n’ type, line nchar or nvarchar, I had much more of a chance of it working right.

    Also, since I was retrieving the data if from an IIS, the client’s IE had to have the right encoding; in my case since I was using Simplified Chinese, the Big5 encoding (which is Traditional Chenese) would display incorrect characters.

    So, all I can say is: 谢谢!

    –Joe

  6. i have 3 table in sql-serverhaving coloumn quantity, i have to deduct the quantity from table1-table2 = table3

    any help me out, how to write the query for this.

    • its Just an example…
      what is your filtering condition that you can add….

      update table3
      set QUANTITY = (select QUANTITY from table1 where id = 3) –
      (select QUANTITY from table2 where id = 3)
      where id = 3

  7. For Arifin Query anwser:

    Unicode string constants that appear in code executed on the server, such as in stored procedures and triggers, must be preceded by the capital letter N. If a string literal is not in Unicode (marked with the N prefix), the string is converted to the default code page of the database, which may not recognize certain characters. With multilingual data it is best to use a Unicode data type and Unicode string literals.

    The following example shows a Unicode string designated by placing an “n” prefix in front of the string:
    Copy Code

    SELECT n’’

    Figure 11 shows how this string, which is the Hindi word for the Hindi language, would appear on a computer that had the correct Hindi font installed.

  8. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 2 Journey to SQL Authority with Pinal Dave

  9. Pingback: SQL SERVER - 2005 Collation Explanation and Translation - Part 2 Journey to SQL Authority with Pinal Dave

  10. Hi Pinal,

    I am trying to implement service broker between two different servers.

    I am using following scripts to create Initiatore service brokers’s object:

    USE master;
    GO
    CREATE MASTER KEY
    ENCRYPTION BY PASSWORD = N”;
    GO

    CREATE LOGIN InitiatorUser with password = ‘Password#123′;

    CREATE USER InitiatorUser ;
    GO

    CREATE CERTIFICATE InstInitiatorCertificate
    AUTHORIZATION InitiatorUser
    WITH SUBJECT = N’Initiator Certificate’,
    START_DATE = N’12/31/2008′;

    BACKUP CERTIFICATE InstInitiatorCertificate
    TO FILE =
    N’C:\InstInitiatorCertificate.cer';
    GO

    IF EXISTS (SELECT * FROM sys.endpoints
    WHERE name = N’InstInitiatorEndpoint’)
    DROP ENDPOINT InstInitiatorEndpoint;
    GO
    CREATE ENDPOINT InstInitiatorEndpoint
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 4022 )
    FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE InstInitiatorCertificate);
    GO

    —I have created following certificate and user on target machine and from target machine I am creating following certificate.

    CREATE LOGIN TargetUser with password = ‘Password#123′;

    CREATE USER TargetUser ;

    CREATE CERTIFICATE InstTargetCertificate
    AUTHORIZATION TargetUser

    FROM FILE =
    N’\\NHQGLBDM001\Ankur\InstTargetCertificate.cer';
    GO

    grant connect on endpoint::InstInitiatorEndpoint to targetuser
    go

    —————Service Broker Objects—————-

    CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
    VALIDATION = WELL_FORMED_XML;
    CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
    VALIDATION = WELL_FORMED_XML;
    GO

    CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]
    (
    [//BothDB/2InstSample/RequestMessage]
    SENT BY INITIATOR,
    [//BothDB/2InstSample/ReplyMessage]
    SENT BY TARGET
    );
    GO

    CREATE QUEUE InstInitiatorQueue;

    CREATE SERVICE [//InstDB/2InstSample/InitiatorService]
    AUTHORIZATION InitiatorUser
    ON QUEUE InstInitiatorQueue;
    GO

    CREATE USER TargetUser WITHOUT LOGIN;

    CREATE CERTIFICATE InstTargetCertificate
    AUTHORIZATION TargetUser
    FROM FILE = ‘\\Nhqglbdm001\Ankur\InstTargetCertificate.cer’
    GO

    CREATE ROUTE InstTargetRoute
    WITH SERVICE_NAME = ‘//TgtDB/2InstSample/TargetService’,
    ADDRESS = ‘TCP://NHQGLBDM001:4022′;

    EXEC (@Cmd);

    SET @Cmd = N’USE msdb’
    CREATE ROUTE InstInitiatorRoute
    WITH SERVICE_NAME =
    ‘[//InstDB/2InstSample/InitiatorService]‘,
    ADDRESS = ‘TCP://PNEITS1C1321D:4022′;

    DECLARE @Cmd NVARCHAR(4000);

    SET @Cmd = N’USE InstInitiatorDB';
    EXEC (@Cmd);
    GO
    CREATE REMOTE SERVICE BINDING TargetBinding
    TO SERVICE
    N’//TgtDB/2InstSample/TargetService’
    WITH USER = TargetUser;

    GO

    ————————–Sending message—————————–
    DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
    DECLARE @RequestMsg NVARCHAR(100);

    BEGIN TRANSACTION;

    BEGIN DIALOG @InitDlgHandle
    FROM SERVICE [//InstDB/2InstSample/InitiatorService]
    TO SERVICE N’//TgtDB/2InstSample/TargetService’
    ON CONTRACT [//BothDB/2InstSample/SimpleContract]
    WITH
    ENCRYPTION = ON;

    SELECT @RequestMsg = N’Message for Target service.';

    SEND ON CONVERSATION @InitDlgHandle
    MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
    (@RequestMsg);

    SELECT @RequestMsg AS SentRequestMsg;

    COMMIT TRANSACTION;
    GO

    following scripts to create Target service brokers’s object:

    USE master;

    CREATE MASTER KEY
    ENCRYPTION BY PASSWORD = N”;
    GO
    CREATE LOGIN TargetUser with password = ‘Password#123′;

    CREATE USER TargetUser ;

    GO

    CREATE CERTIFICATE InstTargetCertificate
    AUTHORIZATION TargetUser
    WITH SUBJECT = N’Target Certificate’,
    START_DATE = N’12/31/2008′;

    BACKUP CERTIFICATE InstTargetCertificate
    TO FILE =
    N’C:\InstTargetCertificate.cer';
    GO

    GO
    IF EXISTS (SELECT * FROM master.sys.endpoints
    WHERE name = N’InstTargetEndpoint’)
    DROP ENDPOINT InstTargetEndpoint;
    GO
    CREATE ENDPOINT InstTargetEndpoint
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 4022 )
    FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE InstTargetCertificate);
    GO

    –Following certificate is already created on Initiator machine and we are creating it from there.

    CREATE LOGIN InitiatorUser with password = ‘Password#123′;

    CREATE USER InitiatorUser ;

    CREATE CERTIFICATE InstInitiatorCertificate
    AUTHORIZATION InitiatorUser
    From FILE =
    N’\\PNEITS1C1321D\shared\InstInitiatorCertificate.cer';

    grant connect on endpoint::InstTargetEndpoint to InitiatorUser

    ———————–Service Broker objects—————

    CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
    VALIDATION = WELL_FORMED_XML;
    CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
    VALIDATION = WELL_FORMED_XML;
    GO

    CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]
    ([//BothDB/2InstSample/RequestMessage]
    SENT BY INITIATOR,
    [//BothDB/2InstSample/ReplyMessage]
    SENT BY TARGET
    );
    GO

    CREATE QUEUE InstTargetQueue;

    CREATE SERVICE [//TgtDB/2InstSample/TargetService]
    AUTHORIZATION TargetUser
    ON QUEUE InstTargetQueue
    ([//BothDB/2InstSample/SimpleContract]);
    GO

    USE InstTargetDB
    GO
    CREATE USER InitiatorUser WITHOUT LOGIN;

    CREATE CERTIFICATE InstInitiatorCertificate
    AUTHORIZATION InitiatorUser
    FROM FILE =
    ‘\\Pneits1c1321d\Shared\InstInitiatorCertificate.cer';
    GO

    DECLARE @Cmd NVARCHAR(4000);

    SET @Cmd = N’USE InstTargetDB';
    CREATE ROUTE InstInitiatorRoute
    WITH SERVICE_NAME =
    ‘//InstDB/2InstSample/InitiatorService’,
    ADDRESS = ‘TCP://Pneits1c1321d:4022′;

    EXEC (@Cmd);

    SET @Cmd = N’USE msdb’
    CREATE ROUTE InstTargetRoute
    WITH SERVICE_NAME =
    ‘//TgtDB/2InstSample/TargetService’,
    ADDRESS = ‘TCP://NHQGLBDM001:4022′;

    EXEC (@Cmd);
    GO
    GRANT SEND
    ON SERVICE::[//TgtDB/2InstSample/TargetService]
    TO InitiatorUser;
    GO
    CREATE REMOTE SERVICE BINDING InitiatorBinding
    TO SERVICE N’//InstDB/2InstSample/InitiatorService’
    WITH USER = InitiatorUser;
    GO

    DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
    DECLARE @RecvReqMsg NVARCHAR(100);
    DECLARE @RecvReqMsgName sysname;

    BEGIN TRANSACTION;

    WAITFOR
    ( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
    FROM InstTargetQueue
    ), TIMEOUT 5000;

    SELECT @RecvReqMsg AS ReceivedRequestMsg;

    IF @RecvReqMsgName = N’//BothDB/2InstSample/RequestMessage’
    BEGIN
    DECLARE @ReplyMsg NVARCHAR(100);
    SELECT @ReplyMsg =
    N’Message for Initiator service.';

    SEND ON CONVERSATION @RecvReqDlgHandle
    MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
    (@ReplyMsg);

    END CONVERSATION @RecvReqDlgHandle with cleanup;
    END

    SELECT @ReplyMsg AS SentReplyMsg;

    COMMIT TRANSACTION;
    GO

    I am able to see message in sys.transmission_queue view but transmission_status field value is coming blank.
    And also can see a row in sys.dm_broker_connections view in CONVERSING state.

    But unable to see message at target machine.

    Could you please provide me a any help on it.
    This very important coz I have to implement in my project.

    Sarita

  11. I have made a table of users with password field. but my problem is that I want nobaby can read that password even the users table is run.

    The table is made in SQL Server 2000.

    Could you please help me providing the code in VB .NET

    with best regards,

    Rabindranath

  12. Rabindranath,

    the answer is simple, don’t store it in plain text to begin with. SQL Server has encryption built in, but better to do one way encyption on the text before storing it – look at hashing and encryption in .Net it pretty straight forward

  13. I am a beginner in SQL Server 2005. I want to work on dual languages in this. Please guide me how can i create a unicode databse and can save data in English and Hindi simulatneously.

    Thanks and Regards
    Allice

  14. sir
    i m using asp .net and sql as a back end
    after connection i have a problem to show hindi fonts from dababase and insert hindi into database

    i inserted water with its hindi meaning and than i got
    ???? marks in data base and when i execute ???? marks hi execute hota hai please help me sir

  15. Hi Atul,

    There are two things to keep in mind while storing unicode data. First the column must be of unicode data type (nchar, nvarchar, ntext). Second the value must be prefixed with N while insertion. For example

    INSERT INTO table (Hindi_col) values (N’hindi data’)

    Regards,
    Pinal Dave

    • Dear Dave,

      Please help me in same case as you helped atul. Same kind of problem i am also facing.

      Actually i m doing a project in which there is a need to use the krutidev 066 font which uses symbol ‘ for the character (sheeti bala sha) and at the backend sqlserver distinguishes the string by separating through same symbol i.e. ( ‘ ).

      I am have used all the parameters as you explained above but still same syntax problem is persist. I have changed datatype from varchar to nvarchar and have used the N prefix but no solution. Please help me….

      Thanks in advance
      Kk

  16. Pinal,

    I am currently working with a set of database done in unicode and with nvarchar fields. I have recently been required to bring in foreign language data (including Japanese, Chinese, etc) that are in varchar fields. Now I need to join and compare the nvarchar fields to the new varchar fields.

    I know that there is a implicit conversion between nvarchar and varchar when joining them together, but in my case the varcahr data was simply not encoded properly and, hence, does not match up with the better nvarchar data.

    How can I cast, convert, collate a the fields to get the nvarchar field to correctly match with the varchar field? Is this possible? Assuming I know the codepage for the data can I upgrage a varchar field into nvarchar? Or downgrade the nvarchar field into a varchar?

    Thanks for the help,
    Mike

  17. Hi pinal,
    Normaly data will be stored as case insensitive in sql server 2008.but for password validation it will give problem right?is there any suggestion for comparision purpose?

  18. sir i have a problem in SQLServer2005 i want to save Hindi Text like in Database and also wants to retrieve hindi text but it will shows me like please help me

  19. hello dave,
    i want to store hindi data in the database but there is no such collation for any indian languages
    please give a solution for it

  20. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 3 of 31 Journey to SQLAuthority

  21. how do i pass hindi parameters in sql server 2008 procedure as i wanna to insert a row with hindi text….there is no option for coalition

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s