The behavior you are seeing here is due, in a general sense, to the fact that the [Unicode Collation Algorithm (UCA)][1] allows for complex, multi-level sorting. More specifically:
1. Sorting is not Comparison:
Determining whether two strings are the same or different is fairly straight forward (given a particular locale/language and set of sensitivities). But determining the order of 2 or more strings can be highly complex.
1. Sorting is done in a series of steps, with each step applied to the entire string, not character by character:
1. Standard: sort base characters (regardless of accent and case differences)
1. IF Accent-sensitive, apply accent / diacritic weights
1. IF Case-sensitive, apply casing weights
When you sort by `col1` (single character), it first determines that all characters have the same weight since they are all "**e**". Next, it applies the accent / diacritic weights. There are no casing differences so the third step wouldn't change anything. So the only differences are in step 2, which is why there is a preferred order for those rows based on `col1`.
When you sort by `col2` (two characters), it first determines that each row has a different weight since both characters are used to determine the sort weight (e.g. "**ea**", "**eb**", etc). Next, it applies the accent / diacritic weights. There are no casing differences so the third step wouldn't change anything. So there are differences in steps 1 and 2 this time. But since the differences in step 1 have already been applied to each string before the weights of step 2 are considered, the weights from step 2 don't have any effect on the ordering; they would only apply if weights from step 1 for two or more rows were the same.
The following adaptation of the sample code from the question hopefully illustrates the sorting behavior described above. I added some additional rows and an additional column to help show the impact of the Collation being case-sensitive (since the original sample data is all lower-case):
**SETUP**
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');
**TEST 1**
SELECT [id], [col1], UNICODE([col1]) AS [CodePoint]
FROM dbo.OddSort
ORDER BY col1;
Returns:
<!-- language: lang-none -->
id col1 CodePoint
1 e 101
5 E 69
8 é 233
3 é 233
7 è 232
2 ê 234
4 ë 235
9 ë 235
6 Ë 203
10 ē 275
What we can see in the results above:
1. The Code Point is not determining the sort order
1. The non-accented characters are sorted before the accented characters (within the same letter: **f** would still come after all of these). Clearly, accent weights are applied before case weights.
1. Lower-case sorts before upper-case within the same accented (or non-accented) character (i.e. the **e** then **E**, and the **ë** then **Ë**). This tailoring is used by most of the Windows Collations, while most of the SQL Server Collations sort upper-case first.
**TEST 2**
<!-- language: lang-sql -->
SELECT [id], [col2]
FROM dbo.OddSort
ORDER BY col2;
Returns:
<!-- language: lang-none -->
id col2
1 eA
8 éB
4 ëC
7 èD
2 êE
10 ēF
5 EG
3 éH
6 ëh
9 ëH
What we can see in the results above:
1. First-level sorting truly is the base characters. If it were accents / diacritics then the **ëC** (id = 4), **ēF** (id = 10), and **EG** (id = 5) rows would not be where they are. If it were casing, then the **EG** (id = 5) row would not be where it is.
1. Second-level sorting truly is the accents / diacritics. This explains why the last three rows are **éH** -> **ëh** -> **ëH** instead of **ëh** -> **éH** -> **ëH** (i.e. IDs 3 -> 6 -> 9 instead of 6 -> 3 -> 9).
1. Third-level sorting truly is the casing. This is why the last 2 rows are **ëh** -> **ëH**, since lower-case sorts first.
**TEST 3**
<!-- language: lang-sql -->
SELECT [id], [col3]
FROM dbo.OddSort
ORDER BY col3;
Returns:
<!-- language: lang-none -->
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
---
**Additional notes:**
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][2] 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][3] makes 2 very interesting points about what is "new" to the `_100_` series Collations:
1. > 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.
1. > 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. 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][2]. To see what Windows / .NET / SQL Server use, you can try the [CompareInfo.GetSortKey Method][4]. 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.
<!-- language: lang-none -->
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]: http://www.unicode.org/reports/tr10/tr10-36.html#Multi_Level_Comparison
[2]: http://demo.icu-project.org/icu-bin/collation.html
[3]: https://msdn.microsoft.com/en-us/library/ms143503(v=sql.100).aspx#Anchor_0
[4]: https://learn.microsoft.com/en-us/dotnet/api/system.globalization.compareinfo.getsortkey