Return to Answer

USE [tempdb];

-- DROP TABLE dbo.OddSort;
CREATE TABLE dbo.OddSort
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    col1 NVARCHAR(5) COLLATE Latin1_General_100_CS_AS,
    col2 NVARCHAR(5) COLLATE Latin1_General_100_CS_AS,
    col3 NVARCHAR(5) COLLATE Latin1_General_100_CS_AS
);
GO

INSERT dbo.OddSort (col1, col2, col3)
VALUES (N'e', N'eA', N'è 1')
     , (N'ê', N'êE', N'ê 5')
     , (N'é', N'éH', N'a 9')
     , (N'ë', N'ëC', N'e 2')
     , (N'E', N'EG', N'E 7')
     , (N'Ë', N'ëh', N'Ë 0')
     , (N'è', N'èD', N'é 4')
     , (N'é', N'éB', N'ē 3')
     , (N'ë', N'ëH', N'e 8')
     , (N'ē', N'ēF', N'ë 6');

id    col3
3     a 9
6     Ë 0
1     è 1
4     e 2
8     ē 3
7     é 4
2     ê 5
10    ë 6
5     E 7
9     e 8

1. Since the datatype is NVARCHAR, we are only concerned with Unicode Code Points and sorting algorithms, hence the use of the UNICODE() function in TEST 1. While Code Pages are specified by most Collations, they only pertain to VARCHAR data. Meaning, while Code Page 1252 is specified by the Latin1_General* series of Collations, that can be ignored here.

USE [tempdb];

-- DROP TABLE dbo.OddSort;
CREATE TABLE dbo.OddSort
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    col1 NVARCHAR(5) COLLATE Latin1_General_100_CS_AS,
    col2 NVARCHAR(5) COLLATE Latin1_General_100_CS_AS,
    col3 NVARCHAR(5) COLLATE Latin1_General_100_CS_AS
);
GO

INSERT dbo.OddSort (col1, col2, col3)
VALUES (N'e', N'eA', N'e A')
     , (N'ê', N'êE', N'ê E')
     , (N'é', N'éH', N'é H')
     , (N'ë', N'ëC', N'ë C')
     , (N'E', N'EG', N'E G')
     , (N'Ë', N'ëh', N'ë h')
     , (N'è', N'èD', N'è D')
     , (N'é', N'éB', N'é B')
     , (N'ë', N'ëH', N'ë H')
     , (N'ē', N'ēF', N'ē F');

id    col3
1     e A
8     é B
4     ë C
7     è D
2     ê E
10    ē F
5     E G
3     é H
6     ë h
9     ë H

What we can see in the results above:

1. The sort order is exactly the same as in Test 2. The only difference in the test values here is that there is a space between each character, removing the possibility of contextual rules. Hence, we know that the reason for the difference in sort order for col2 in the question is again due to "Multi-Level Comparison", and not "Contextual Sensitivity".

1. Since the datatype is NVARCHAR, we are only concerned with Unicode Code Points and sorting algorithms, hence the use of the UNICODE() function in TEST 1. While Code Pages are specified by most Collations, they only pertain to VARCHAR data. Meaning, while Code Page 1252 is specified by the Latin1_General* series of Collations, that can be ignored here.

2. The weights described in the Default Unicode Collation Element Table (DUCET) (Version 5.0.0 which should map to the _100_ series Collations) are fine for US English, but not other locales / languages. Other languages need to start with the DUCET and then apply locale-specific override rules as defined by the Common Locale Data Repository (CLDR) project. From what I can tell, versions 1.4 / 1.4.1 were released in 2006. To get those overrides, download the CLDR 1.4 "core" file via http://unicode.org/Public/cldr/1.4.0/core.zip, then, in that zip file, go to the collation folder and find the XML file corresponding to the locale being used. Those files contain just the overrides, and are not complete sets of collation rules.

1. Since the datatype is NVARCHAR, we are only concerned with Unicode Code Points and sorting algorithms, hence the use of the UNICODE() function in TEST 1. While Code Pages are specified by most Collations, they only pertain to VARCHAR data. Meaning, while Code Page 1252 is specified by the Latin1_General* series of Collations, that can be ignored here.

1. Sort Keys used by Windows / .NET / SQL Server are not exactly the same as shown in the Unicode Standard (See @Patrick's answer) or implemented in the ICU. To see what Windows / .NET / SQL Server use, you can try the CompareInfo.GetSortKey Method. I created a SQLCLR UDF to pass in these values and get the sort key. Please note that I am using SQL Server 2017 on Windows 10 with .NET Framework 4.5 - 4.6.1 installed, so .NET should be using Unicode 6.0.0. Also, Level4 is not being used for these strings.

    CHAR    L1     L2     L3     L4
    e      0E21
    E      0E21           12
    ë      0E21    13
    Ë      0E21    13     12
   

Looking at these sort keys for Test 1, and realizing that the levels are sorted like multiple columns within an ORDER BY clause (L3 is sorted within same values of L2, which is sorted within same values of L1), should illustrate that the reason for the behavior noted in the question is in fact the multi-level sorting capability of Unicode. Likewise:

<!-- language: lang-none -->

    CHAR       L1         L2       L3       L4
    EG      0E210E25              1212
    éH      0E210E2C      0E      0212
    ëh      0E210E2C      13
    ëH      0E210E2C      13      0212

Looking at some of the sort keys for Test 2, we can see that base characters are sorted first (L1), then accents are sorted (L2), and then casing is sorted (L3).

1. Since the datatype is NVARCHAR, we are only concerned with Unicode Code Points and sorting algorithms, hence the use of the UNICODE() function in TEST 1. While Code Pages are specified by most Collations, they only pertain to VARCHAR data. Meaning, while Code Page 1252 is specified by the Latin1_General* series of Collations, that can be ignored here.

1. With regarding to getting the exact rules, that is not as easy as it should be. The problem with getting concrete explanations of these rules is that the Unicode sorting rules, while definitely documented, are a recommendation. It is up to vendors, such as Microsoft, to implement those recommendations. Microsoft did not implement the recommendations exactly as stated in the Unicode documentation so there is a disconnect there (similar to how neither the HTML or CSS specifications are implemented as completely, nor even in the same way, across vendors). Then, there are different versions of the Windows Collations (you are using version 100 which came out with SQL Server 2008) and that is tied to a Unicode version that is much older than the current version of Unicode or of the ICU Collation demo is using. Still, the UCA documentation linked above is a good place to start.

2. Since the datatype is NVARCHAR, we are only concerned with Unicode Code Points and sorting algorithms, hence the use of the UNICODE() function in TEST 1. While Code Pages are specified by most Collations, they only pertain to VARCHAR data. Meaning, while Code Page 1252 is specified by the Latin1_General* series of Collations, that can be ignored here.

1. With regarding to getting the exact rules, that is not as easy as it should be. The problem with getting concrete explanations of these rules is that the Unicode sorting rules, while definitely documented, are a recommendation. It is up to vendors, such as Microsoft, to implement those recommendations. Microsoft did not implement the recommendations exactly as stated in the Unicode documentation so there is a disconnect there (similar to how neither the HTML or CSS specifications are implemented as completely, nor even in the same way, across vendors). Then, there are different versions of the Windows Collations (you are using version 100 which came out with SQL Server 2008) and that is tied to a Unicode version that is much older than the current version of Unicode or of the ICU Collation demo is using. For example, the What's New in SQL Server 2008 Collations section of the "Collation and Unicode Support" documentation for SQL Server 2008 makes 2 very interesting points about what is "new" to the _100_ series Collations:

2. Unicode 5.0 case table.

   Unicode 5.0 was published in July of 2006 (well, the character database was released then, and the full spec followed in late 2006). The current version is 10.0 which was published in June of 2017. And given the release pattern of the past 4 years, it is likely that version 11.0 will be out sometime mid-2018.

3. Weighting has been added to previously non-weighted characters that would have compared equally.

   Those weights were more than likely defined in the Unicode Standard, just not in this implementation of it.

 
Still, the UCA documentation linked above is a good place to start.

1. Since the datatype is NVARCHAR, we are only concerned with Unicode Code Points and sorting algorithms, hence the use of the UNICODE() function in TEST 1. While Code Pages are specified by most Collations, they only pertain to VARCHAR data. Meaning, while Code Page 1252 is specified by the Latin1_General* series of Collations, that can be ignored here.