Mfcttrf

Which SQL Server collation should I use for biblical Hebrew? The database under consideration needs to accommodate diacritics (i.e., vowels, accents, trope, etc.).

First, regardless of anything else, you want to use the newest set of collations, which are the _100_ series as they have the newer / more complete sort weights and linguistic rules than the older series with no version number in the name (technically the are version 80). (NOTE: testing is showing that the version 100 collations are not allowing cantillation marks to be ignored while the non-versioned collations are, which is the opposite of how it should be...will update with what I find out)

Next, it depends on how you will be interacting with the string values. Hebrew has no case variations, nor do the concepts of "width" or "Kana" apply. Accent-sensitive / insensitive might apply to diacritics used for vowels and cantillation marks (i.e. trope). Maybe answer these questions:

1. 
   

   Do you need to distinguish between א and אֽ, or between ש, שׁ, and שׂ? If so, you want an _AS collation. (actually, testing is showing that vowels and cantillation marks are always sensitive in version 100 collations, but not in unversioned / version 80 collations, which is the opposite of how this should be). The following test shows that a vowel difference still compares as different in a newer, accent-insensitive collation, which I think should compare as the same:

   
   
   

   SELECT NCHAR(0x05D0), NCHAR(0x05D0) + NCHAR(0x05BD)
   WHERE NCHAR(0x05D0) = NCHAR(0x05D0) + NCHAR(0x05BD) COLLATE Hebrew_100_CI_AI_SC;
   -- no rows (was expecting 1 row!!)
   
   SELECT NCHAR(0x05D0), NCHAR(0x05D0) + NCHAR(0x05BD)
   WHERE NCHAR(0x05D0) = NCHAR(0x05D0) + NCHAR(0x05BD) COLLATE Hebrew_CS_AI;
   -- 1 row (expected)
   -- א אֽ
   
   SELECT NCHAR(0x05D0), NCHAR(0x05D0) + NCHAR(0x05BD)
   WHERE NCHAR(0x05D0) = NCHAR(0x05D0) + NCHAR(0x05BD) COLLATE Hebrew_CS_AS;
   -- no rows (expected)
   

   
   
   

   Even just a cantillation mark difference is an issue with the newer accent-insensitive collations, and this seems odd given that the mark is Hebrew Accent Geresh (U+059C) (which has "accent" in the name!):

   
   
   

   SELECT NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05BD),
    NCHAR(0x05D0) + NCHAR(0x05BD)
   WHERE NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05BD) =
    NCHAR(0x05D0) + NCHAR(0x05BD) COLLATE Hebrew_100_CS_AI_SC;
   -- no rows (was expecting 1 row!!)
   
   SELECT NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05BD),
    NCHAR(0x05D0) + NCHAR(0x05BD)
   WHERE NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05BD) =
    NCHAR(0x05D0) + NCHAR(0x05BD) COLLATE Hebrew_CS_AI;
   -- 1 row (expected)
   -- אֽ֜ אֽ
   
   SELECT NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05BD),
    NCHAR(0x05D0) + NCHAR(0x05BD)
   WHERE NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05BD) =
    NCHAR(0x05D0) + NCHAR(0x05BD) COLLATE Hebrew_CS_AS;
   -- no rows (expected)
   

   
   



2. Do you need to distinguish between upper-case and lower-case in other languages, such as English? If so, then you want a _CS collation, else you can use a _CI collation (i.e. case-insensitive)

You do not want a binary collation (_BIN or _BIN2) as those cannot distinguish between Hebrew letters with both vowels and cantillation marks that are the same but have the combining characters in different orders, nor can they ignore vowels and other marks to equate things like א and אֽ.

For example (vowel and cantillation mark combining characters in opposite order):

SELECT NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05BD),
 NCHAR(0x05D0) + NCHAR(0x05BD) + NCHAR(0x059C)
WHERE NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05BD) =
 NCHAR(0x05D0) + NCHAR(0x05BD) + NCHAR(0x059C) COLLATE Hebrew_100_CS_AS_SC;
-- אֽ֜ אֽ֜

SELECT NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05BD),
 NCHAR(0x05D0) + NCHAR(0x05BD) + NCHAR(0x059C)
WHERE NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05BD) =
 NCHAR(0x05D0) + NCHAR(0x05BD) + NCHAR(0x059C) COLLATE Hebrew_100_BIN2;
-- no rows

Also, the collations ending in _SC support supplementary characters (i.e. full UTF-16) so usually best to pick one of those, if available.

So, perhaps Hebrew_100_CI_AS_SC or Hebrew_100_CS_AS_SC for the columns, and you can override per expression / predicate via the COLLATE statement if you need to use a variation, such as COLLATE Hebrew_CS_AI.

Also, you will need to store the data in NVARCHAR columns / variables.

It depends on a lot of things. Collation is sorting, comparing, and non-unicode code page.

This repo has a good list of options around Hebrew.

+---------------------------+---------------------------------------------------------------------------------------------------------------------+
| Hebrew_BIN | Hebrew, binary sort |
| Hebrew_BIN2 | Hebrew, binary code point comparison sort |
| Hebrew_CI_AI | Hebrew, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive |
| Hebrew_CI_AI_WS | Hebrew, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive |
| Hebrew_CI_AI_KS | Hebrew, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive |
| Hebrew_CI_AI_KS_WS | Hebrew, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive |
| Hebrew_CI_AS | Hebrew, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive |
| Hebrew_CI_AS_WS | Hebrew, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive |
| Hebrew_CI_AS_KS | Hebrew, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive |
| Hebrew_CI_AS_KS_WS | Hebrew, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive |
| Hebrew_CS_AI | Hebrew, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive |
| Hebrew_CS_AI_WS | Hebrew, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive |
| Hebrew_CS_AI_KS | Hebrew, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive |
| Hebrew_CS_AI_KS_WS | Hebrew, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive |
| Hebrew_CS_AS | Hebrew, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive |
| Hebrew_CS_AS_WS | Hebrew, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive |
| Hebrew_CS_AS_KS | Hebrew, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive |
| Hebrew_CS_AS_KS_WS | Hebrew, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive |
| Hebrew_100_BIN | Hebrew-100, binary sort |
| Hebrew_100_BIN2 | Hebrew-100, binary code point comparison sort |
| Hebrew_100_CI_AI | Hebrew-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive |
| Hebrew_100_CI_AI_WS | Hebrew-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive |
| Hebrew_100_CI_AI_KS | Hebrew-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive |
| Hebrew_100_CI_AI_KS_WS | Hebrew-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive |
| Hebrew_100_CI_AS | Hebrew-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive |
| Hebrew_100_CI_AS_WS | Hebrew-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive |
| Hebrew_100_CI_AS_KS | Hebrew-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive |
| Hebrew_100_CI_AS_KS_WS | Hebrew-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive |
| Hebrew_100_CS_AI | Hebrew-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive |
| Hebrew_100_CS_AI_WS | Hebrew-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive |
| Hebrew_100_CS_AI_KS | Hebrew-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive |
| Hebrew_100_CS_AI_KS_WS | Hebrew-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive |
| Hebrew_100_CS_AS | Hebrew-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive |
| Hebrew_100_CS_AS_WS | Hebrew-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive |
| Hebrew_100_CS_AS_KS | Hebrew-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive |
| Hebrew_100_CS_AS_KS_WS | Hebrew-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive |
| Hebrew_100_CI_AI_SC | Hebrew-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive, supplementary characters |
| Hebrew_100_CI_AI_WS_SC | Hebrew-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive, supplementary characters |
| Hebrew_100_CI_AI_KS_SC | Hebrew-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive, supplementary characters |
| Hebrew_100_CI_AI_KS_WS_SC | Hebrew-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive, supplementary characters |
| Hebrew_100_CI_AS_SC | Hebrew-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters |
| Hebrew_100_CI_AS_WS_SC | Hebrew-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive, supplementary characters |
| Hebrew_100_CI_AS_KS_SC | Hebrew-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive, supplementary characters |
| Hebrew_100_CI_AS_KS_WS_SC | Hebrew-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters |
| Hebrew_100_CS_AI_SC | Hebrew-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive, supplementary characters |
| Hebrew_100_CS_AI_WS_SC | Hebrew-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive, supplementary characters |
| Hebrew_100_CS_AI_KS_SC | Hebrew-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive, supplementary characters |
| Hebrew_100_CS_AI_KS_WS_SC | Hebrew-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive, supplementary characters |
| Hebrew_100_CS_AS_SC | Hebrew-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters |
| Hebrew_100_CS_AS_WS_SC | Hebrew-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive, supplementary characters |
| Hebrew_100_CS_AS_KS_SC | Hebrew-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive, supplementary characters |
| Hebrew_100_CS_AS_KS_WS_SC | Hebrew-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters |
+---------------------------+---------------------------------------------------------------------------------------------------------------------+