Does a collation have any influence over a query speed? Does the size of a table change depending of the collation?

If I want to build a website that must support all possible languages (lets take for e.g. Google) which would be the recommended collation?

I will need to store characters such as 日本語, my searches over the website will have to return something for the sóméthíng input, it must be case insensitive as well.

How do I know which is the best choice to make? Which collation better suits this case?

  • 4
    You might want to rephrase the question so that it doesn't sound so subjective - "best" collation by what measure? :)
    – TML
    Jan 5 '11 at 6:47
  • The new title reads much better
    – TML
    Jan 5 '11 at 8:10

Generally speaking, one of the Unicode variants is probably the best for broad language support - UTF-8 is going to use less memory per codepoint, and thus will have a slight advantage in any time/space tradeoffs you find yourself in need of making; however, I think there are some of the more esoteric languages/scripts that UTF-8 cannot represent (but I'm not 100% certain of that, I haven't done an exhaustive study on the matter).

This Wikipedia article might be enlightening on the dis/advantages of each.

  • Yes, UTF-8 can handle the 1.1 million Unicode code points.
    – vz0
    Jan 14 '11 at 15:51
  • Thanks - I thought there were some of the Han characters or the like that were not supported in UTF-8, good to have a solid answer.
    – TML
    Jan 20 '11 at 1:21

I believe that you should use a Unicode collation that is accent and case insensitive. Please read the MSDN articles Selecting Collation and Using sql collations and all linked articles.


I think the question as stated (on 2015-04-20, "Which collation [...]") is not what is meant, given that the accepted answer talks about encoding rather than collation. Let me answer the stated question rather than the intended one, just because I think it is interesting :-)

Wikipedia says "Collation is the assembly of written information into a standard order". In computing, collation has taken on the meaning of "a specification of such an order". In other words, a collation is (or implies) a definition of a three-way comparison function.

I think the short answer is "definitely maybe". At least I'm aware of the following shenanigans:

name = u"Jonas K\xf6lker" # \xf6 is o-umlaut
enc = name.encode('utf-8')
assert len(name) == 12  # \xf6 is one character
assert len(enc) == 13   # but two bytes in utf-8

import locale
locale.setlocale(locale.LC_COLLATE, "da_DK.utf8") # works on my machine
long_form = locale.strxfrm(enc)
assert len(long_form) == 38

locale.strxfrm is a function which Returns a string that behaves for cmp locale-aware, that is, it encodes a string such that a byte-by-byte standard lexicographic comparison against another string encoded similarly will produce the same result as comparing strings according to the collation function specified by the locale.

Some observations: in da_DK.utf8, the string ouüö is sorted. In de_DE.utf8, the string oöuü is sorted. Note that len(long_form) == 38 and 38 > 13. (The length is also 38 in de_DE.utf8.)

If your database has an index on some string field, collated according to da_DK.utf8, it may internally be doing something like strxfrm in order to have a simple comparison. (On the other hand, disks are slow. It may be faster to index based on a more compact representation, if a higher per-character comparison cost is more than offset by comparing fewer characters.)

You ask "Does a collation have any influence over a query speed?", to which I'm pretty sure the answer is yes: the "C" (aka "POSIX") collation just compares unicode code point values, whereas the Danish (da_DK.utf8) and German (de_DE.utf8) locales do something more tricky. This will have some impact on query speed, although I suspect it won't be worth worrying about.

"Does the size of a table change depending of the collation?" — I can imagine having an index according to one collation and a different index according to another collation, or just one of such two indices, with some strxfrm-like transformation applied. In that hypothetical scenario, if there are two collations with different size characteristics, the answer is yes.

"which would be the recommended collation?" — That depends on why you would need to sort strings. If it's only to have some canonical way of ordering strings, I'd probably go with "C". If it's to present data to the users in sorted order according the human's expectations, and those expectations are shaped by their culture, and you want the database (and not some other layer) to do the sorting, perhaps you should build one index per collation, i.e. at least one according to da_DK.utf8 for the Danes and one according to de_DE.utf8 for the Germans. I think this might get fairly big fairly quickly, though.

All of this is highly dependent on the inner workings of your database; I think it goes well beyond "standardized" (lol!) SQL. As always, consult the documentation to your specific database system.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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