title: SQL Server 2005: Need advice on handling multiple-language data in one table column
body: <p>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.</p>
  
  <p>My database is using the default collation of SQL_Latin1_General_CP1_CI_AS,
  as most of our data is in English.</p>
  
  <p>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.</p>
  
  <p>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...</p>
  
  <p>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.</p>