SQL Server 2005: Need advice on handling multiple-language data in one table column

Question

I'm running SQL Server 2005 and .Net 2.0. I have some tables that need to store data in multiple languages, in the same column. E.g. an Article table, with ArticleID (identity) and ArticleName (nvarchar 100) The data in ArticleName might be in various languages.

My database is using the default collation of SQL_Latin1_General_CP1_CI_AS, as most of our data is in English.

Each user in my application can select his/her preferred culture. In the user interface, the articles data is displayed in a data grid and the user can sort them by ArticleName.

Right now, the sort order is not correct because of my database collation. I am thinking of fixing this by using the ORDER BY ... COLLATE clause. But how do I map the correct user culture to the SQL Server collation? Do I have to specify for each culture, like this - eg. if culture = zh-CN (Chinese PRC), then use collation Chinese_PRC_CI_AS and so on...

Is there a better way to get the collation? Is my approach correct? Or is there a better way to handle this? (I did think of actually performing the sorting in .Net, but that seems like a waste of CPU time.) Thanks for reading.

Answer 1

SQL Server 2005 has a fn_helpcollations() table function, which returns all supported collations:

select *, 
COLLATIONPROPERTY(name, 'CodePage') as CodePage,
COLLATIONPROPERTY(name, 'LCID') as LCID,
COLLATIONPROPERTY(name, 'ComparisonStyle') as ComparisonStyle,
COLLATIONPROPERTY(name, 'Version') as Version
from fn_helpcollations()

This MSDN article deals with SQL Server collations in detail and states:

However, because there is no direct mapping between LCID and collations, you cannot determine the correct collation from an LCID.

This is inconvenient if you would like to be able automatically to assign the best sorting order for users, based on their locale ID.

I suggest you build a custom table mapping SQL collations to the languages you support for your users, retrieve the user's collation from this mapping table, and use ORDER BY COLLATE as you intended.