We have an accounting program with an SQL Server database and would like to start a Ukrainian version. I am not sure which collation to use: Ukrainian or Cyrillic_General?

With a non-technical Ukrainian, we compared the collation charts for Cyrillic_general and Ukrainian and found only minor differences towards the end of the table. That person thinks Cyrillic_general should do fine.

Also, SQL Server 2012 no longer lists the Ukrainian collation. Is this a reason for concern?

Any advice which to choose?

  • 4
    Missing Ukrainian collation on MSDN seems to be documentation issue. SELECT * FROM sys.fn_helpcollations() sure shows it present. – Remus Rusanu Aug 26 '15 at 13:00
up vote 4 down vote accepted

First, just to make sure that a collation is available or not, you can test in two ways:

  • Check the list of available collations (i.e. sys.fn_helpcollations()). Running the following query in SQL Server 2012 and 2014 should return 52 rows:

    SELECT * FROM sys.fn_helpcollations() WHERE [name] LIKE N'ukrain%';
    
  • Try using the collation:

    SELECT N'a' COLLATE Ukrainian_100_CI_AS;
    

    If a collation is not available, it will let you know ;-)

Next, we should see if the collations work as expected (best not to fully trust documentation, right ;-) ?).

To test these two collations, run the following:

DECLARE @CollationTest TABLE (Col1 NVARCHAR(5));
INSERT INTO @CollationTest (Col1) VALUES (N'Ы'), (N'Ю'), (N'Ь'), (N'Ъ'), (N'Я'), (N'Э');

SELECT Col1 AS [Ukrainian_100_CS_AS]
FROM @CollationTest
ORDER BY Col1 COLLATE Ukrainian_100_CS_AS;

SELECT Col1 AS [Cyrillic_General_100_CS_AS]
FROM @CollationTest
ORDER BY Col1 COLLATE Cyrillic_General_100_CS_AS;

Results:

Ukrainian_100_CS_AS
----------------------------
Ъ
Ы
Э
Ю
Я
Ь


Cyrillic_General_100_CS_AS
----------------------------
Ъ
Ы
Ь
Э
Ю
Я

Those two orderings at least match the collations linked in the question (which makes sense given that those collation charts were done in 2009 and the _100 series of collations came out in SQL Server 2008).

The only difference seems to be in the initial sort order of one character: Ь (Capital Letter Soft Sign). Is the placement of one character a determining factor? You do need to mirror, as much as possible, the expectations of your customers. But, keep in mind that sort rules and initial ordering changes over the years.

Here are some examples of the Unicode spec for the Ukrainian locale. As you can see, both placements have been correct, though the "current" placement matches the Cyrillic collation, not the Ukrainian collation. The main release history of the locale-specific data starts here: CLDR Releases/Downloads

http://www.unicode.org/repos/cldr/tags/release-1-4/posix/uk_UA.UTF-8.src  (2006)
<CYRILLIC_CAPITAL_LETTER_SOFT_SIGN>                    <X5EF0>;<X0005>;<X008F>;IGNORE -- Ь
<CYRILLIC_CAPITAL_LETTER_YA>                           <X5EFC>;<X0005>;<X008F>;IGNORE -- Я

http://www.unicode.org/repos/cldr/tags/release-1-9/posix/uk_UA.UTF-8.src  (2010)
<CYRILLIC_CAPITAL_LETTER_YA>                           <X5D45>;<X05>;<X8F>;IGNORE -- Я
<CYRILLIC_CAPITAL_LETTER_SOFT_SIGN>                    <X5D46>;<X05>;<X86>;IGNORE -- Ь

http://www.unicode.org/repos/cldr/tags/release-21/posix/uk_UA.UTF-8.src   (2011)
<CYRILLIC_CAPITAL_LETTER_YA>
                           <X5D45>;<X05>;<X8F>;<CYRILLIC_CAPITAL_LETTER_YA>        -- Я
<CYRILLIC_CAPITAL_LETTER_SOFT_SIGN>
                           <X5D46>;<X05>;<X86>;<CYRILLIC_CAPITAL_LETTER_SOFT_SIGN> -- Ь

http://www.unicode.org/repos/cldr/tags/release-24/common/uca/allkeys_CLDR.txt  (2013)
042C  ; [.1B43.0020.0008.042C] # CYRILLIC CAPITAL LETTER SOFT SIGN -- Ь
042F  ; [.1B5E.0020.0008.042F] # CYRILLIC CAPITAL LETTER YA        -- Я

http://www.unicode.org/repos/cldr/tags/release-27/common/uca/allkeys_CLDR.txt  (2015)
042C  ; [.1E56.0020.0008] # CYRILLIC CAPITAL LETTER SOFT SIGN -- Ь
042F  ; [.1E6D.0020.0008] # CYRILLIC CAPITAL LETTER YA        -- Я

And to be fair, we should keep in mind that collations handle more than sorting. They handle casing, transformations, etc.


So what should one do? It is probably best, regardless of the rest of this, to choose the locale that is most common for your customers to be using as their main Windows OS locale. This will at least give consistent behavior to how everything else should be running for them. Run the following to see on your system:

SELECT os.os_language_version AS [WindowsLocale],
       COLLATIONPROPERTY(N'Ukrainian_100_CS_AS', 'lcid') AS [SQLServerLocale-Ukrainian],
    COLLATIONPROPERTY(N'Cyrillic_General_100_CS_AS', 'lcid') AS [SQLLocale-CyrillicGeneral]
FROM   sys.dm_os_windows_info os;

Ask as many of your customers / potential customers as possible to check their Windows setup so that you can plan accordingly.

  • 1
    I don't think the number of locales a collation applies to should determine your decision. It is true that the difference between the two collations in question seems minor, but it is there nevertheless. It all depends on the target audience, I think. Perhaps the OP's Ukrainian mate is bi-lingual (Russian/Ukrainian), as many are in Ukraine, as I am, in fact. That doesn't mean the established sorting rules should be ignored. Just saying. – Andriy M Aug 26 '15 at 16:05
  • @AndriyM I completely agree. That's why I said it is merely something to consider. I was not dismissing the difference, only saying that if it were not a truly important difference, then all things being equal, go with the one that applies to the larger audience. The question merely states that there is a minor difference. If that difference were in the available characters, then clearly you need to choose the one that has what you need. But it is possible that with all of the same characters, a small sort difference between 3 of the characters might be meaningless. So I was just mentioning. – Solomon Rutzky Aug 26 '15 at 16:51
  • The Ukrainian version will be used only in Ukraine, as the program is localized for each country. Now it seems more clear to me that the Ukrainian collation is the preferable option, thanks to your input. I was kinda hoping to keep down the number of collations we use (we already use Cyrillic_general for Bulgaria, which has no separate collation) and to reuse the same SQL Server instance for Ukrainian. As @AndriyM says, the different collations are there for a reason. – Primoz Aug 27 '15 at 8:22
  • 1
    @Primoz: There have been disputes (possibly still going on) among Ukrainians about the "correct" position of Ь/ь in the alphabet. So actually your question is very reasonable and may not really have an easy answer. However, sticking to the fact that there is a specific Ukrainian collation that is related to the Ukrainian locale (and there are no other collations that apply to that locale, based on the chart here) seems a perfectly safe approach to me. – Andriy M Aug 27 '15 at 8:44
  • 1
    I hadn't read the latest version of your answer at the time of my second comment. Whatever might have looked as if I still disagreed with you was merely just a further clarification of my point in response to @Primoz. Sorry if my comment gave you the wrong impression. I've read your answer now and it makes sense to me. Thanks for taking the trouble of clarifying your point. – Andriy M Aug 27 '15 at 20:57

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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