Deal with Indian regional language in SQL Server 2005/2008

Yesterday, I have received one email for help about querying and filtering records contain Telugu Indian regional language. Have a look:
The problem was when person filtering records of telugu language, was not able to get any single records. You have to deal with few things while dealing with regional languages.
1.)    If whole database should be in regional language, set proper collation for that.
2.)    Set proper collation for column
3.)    While querying or inserting data, MUST USE “N” as a prefix as regional data should be in Unicode and “N” prefix is must to differentiate whether data is Unicode.
When I asked that person, what collation are you using for database and column? Below is the answer:
=========================================================================
Hi Ritish,
                Yes.I have set Indic_General_90_BIN as Proper collation to Telugu Column.
                 But I am not able to get the records.
                 Is there any way to get the records please ? 
=========================================================================
What is the reason if proper collation is set even though data doesn’t get filtered by following query?
SELECT * FROM TeluguDictionary where (Telugu like ‘%అక్క%’)


Or doesn’t insert proper data with following INSERT statement?
INSERT INTO TeluguDictionary VALUES (‘akkadi’,అక్కడి)


“N” prefix was missing in both query. If s/he would have used same query with “N” prefix like below, it would have worked.
SELECT * FROM TeluguDictionary where (Telugu like N‘%అక్క%’)
INSERT INTO TeluguDictionary VALUES (‘akkadi’,Nఅక్కడి)
Reference: Ritesh Shah

Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Advertisements

Change collation in SQL Server 2005/2008

Collation: It’s nothing but the set of rules that defined how data is stored and compared.
SQL Server default uses collation “SQL_Latin1_General_CP1_CI_AS” which is:
–The ISO code page 1252.
–The dictionary order, case-insensitive character sort order.
–The General Unicode collation



If you want to change this default collation to your own regional collation for your database, you can use below given script.
Alter Database AdventureWorks –give your database name
Collate SQL_LATIN_GENERAL1_CI_AS –give collation you want to apply
 

You may face the error while running above script sometime if your database is in multi user mode, in that case you have to set your database to single user mode, change collation and set your database to multi user mode. Look at the script below.



 Alter Database AdventureWOrks set single_user
Alter Database AdventureWorks –give your database name
Collate SQL_LATIN_GENERAL1_CI_AS –give collation you want to apply
Alter Database AdventureWOrks set multi_user



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com