3

After decades of business speaking with companies that adhere to the general Latin1 Collation, my company is facing the issue of storing information in a different charset and collation: Greek. So, it's time to start thinking a sort of redesign of our dbs.

Given that my installation is a MS SQLServer 2008 R2, what are the best methods, or general accepted guide lines, to do such a thing? Multiple tables? Multiple Dbs with different settings?

I'm not a DBA, I'm only asking to have a starting point to mumble upon.

Thank you very much for reading and to all who will care to reply.

Improve this question
2
  • 1
    Do you want everything greek, or just a few columns? For these, will multiple languages be stored, or is the whole column(s) to be greek?
    – gbn
    Oct 27, 2011 at 11:23
  • 1
    The best possible solution will be to mantain the current structure and have the possibility to store non latin charachter in the same column where latin ones are.
    – vaitrafra
    Oct 27, 2011 at 15:14

2 Answers 2

Reset to default
1

Quote from this MS tech-page:

If the users of your instance of SQL Server speak multiple languages, you should pick a collation that best supports the requirements of the various languages. For example, if the users generally speak western European languages, choose the Latin1_General collation. When you support users who speak multiple languages, it is most important to use the Unicode data types, nchar, nvarchar, and ntext, for all character data. Unicode was designed to eliminate the code page conversion difficulties of the non-Unicode char, varchar, and text data types. Collation still makes a difference when you implement all columns using Unicode data types because it defines the sort order for comparisons and sorts of Unicode characters. Even when you store your character data using Unicode data types you should pick a collation that supports most of the users in case a column or variable is implemented using the non-Unicode data types.

So, just like @Gonsalu said in a comment to @TechiGurl: build your database for Unicode to support multiple languages.

In practice this means using nchar/nvarchar/ntext datatypes, and not char/varchar/text.

Choosing a collation is a matter of query-optimization. Any collation will order your text in a definitive manner. Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales. Thus you should choose a collation that best serves the language-requirements of most of the queries being run on your database.

In the case given by the OP, if Greek-Text now makes up 10% of all data, then I would stay with the Latin1 Collation. If there is more Greek-Language data or most of the queries being run on the database retrieve Greek-Data, then I would go with Greek_ Collation.

Here is a list of default MS Collations.

Improve this answer
2

If the data is the same (same tables/columns) and the only change is that now there is the possibility of more languages being used, I'd say go for UTF-8. Force it as the default charset on all the table definitions (new ones at least), and set it in your config to be used for client connections that don't specify a charset.

You have not specified what DB type you are using (MySQL, SQL Server…etc) so I am trying to be as general as can be :)

Improve this answer
4
  • Oh, i definetely forgot to mention my installation is a MS SQL Server 2008 R2.
    – vaitrafra
    Oct 27, 2011 at 15:20
  • Sadly I don't know enough MS SQL to give you exact commands/config lines. But I think the same principle should apply. UTF-8 is the most universal charset and will store all non latin characters properly.
    – TechieGurl
    Oct 27, 2011 at 15:25
  • I'm not searching for scripts, these will come later and code is basically the easiest part in evry project. (and btw, i will not write a line of it for this prj :) I am starting data collection to ensure the whole thing will be done in one of the best way possible.
    – vaitrafra
    Oct 27, 2011 at 15:28
  • 3
    SQL Server does not support UTF-8; it has the NVARCHAR data type which supports Unicode using UCS2 character encoding.
    – gonsalu
    Oct 28, 2011 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.