SQL Server: Ambiguous Sort Order for Case-Insensitive Sorting

Question

I have two instances of SQL Server database (same version) on two computers. The database matching value is case insensitive . For example, I have 3 records in a table with a name column.

SQL Server Instance 1

 > select name from table1 order by name name ----- Adam adam Adam 

SQL Server Instance

 > select name from table1 order by name name ----- Adam adam Adam 

Why is sorting ambiguous on two nodes of the same sql server database? Why is this? What is the sorting algorithm?

Answer 1

If the sort is case insensitive, all three values ​​are explicitly considered identical, and there is no guarantee regarding the order of identical ordered rows; it cannot be like there is no reason to make this decision, there is no "natural" order.

The observed order should be considered random and applies to internal servers, for example, what data is available in the read / buffer caches, I / O queues, etc.