ORDER BY in SQL Server vs Postgresql

Question

I am trying to run ORDER BY on a huge dataset and create dense rank values ​​to extract a different number of rows based on dense rank. In general, I use the dense ranking value as a surrogate key in my entire process to transfer intermediate results and use them for some performance checks.

The problem I am facing: -

The problem I encountered is moving the dataset from Matrix (postgresql) (PRODUCTION environment) to SQL Server (new environment) and the dense ranking function is implemented. But due to the presence of different dense rank results, which are caused by the behavior of ORDER BY in SQL Server and Matrix, I can not continue checking the code and results.

Test example:

SELECT *,DENSE_RANK() OVER ( ORDER BY Col ) AS drnk FROM 
(
SELECT '#2 Mayfair Terrace' AS Col
UNION 
SELECT '#2 MYSTIC COURT' AS Col
)Z;

SQL Server output:

Col                drnk
#2 Mayfair Terrace  1
#2 MYSTIC COURT     2

Matrix (Postgresql): -

Col                drnk
#2 MYSTIC COURT     1
#2 Mayfair Terrace  2

- , ORDER BY ? . .

Answer 1

collation. collate

select *
 , dense_rank() over (
    order by col collate sql_latin1_general_cp437_bin
    ) as drnk
from (
 select '#2 Mayfair Terrace' as col
 union all
 select '#2 MYSTIC COURT' as col
 ) Z;

+--------------------+------+
|        col         | drnk |
+--------------------+------+
| #2 MYSTIC COURT    |    1 |
| #2 Mayfair Terrace |    2 |
+--------------------+------+

rextester: http://rextester.com/QGM99129

select *
 , dense_rank() over (
    order by col collate 
          sql_latin1_general_cp437_bin
    ) as drnk
from (
 select '#2 Mayfair Terrace' as col
 union all select '#2 MYSTIC COURT' as col
 union all select '#2 NAYSTIC COURT' as col
 union all select '#2 NaYSTIC COURT' as col
 union all select '#2 LaYSTIC COURT' as col
 ) Z;

+--------------------+------+
|        col         | drnk |
+--------------------+------+
| #2 LaYSTIC COURT   |    1 |
| #2 MYSTIC COURT    |    2 |
| #2 Mayfair Terrace |    3 |
| #2 NAYSTIC COURT   |    4 |
| #2 NaYSTIC COURT   |    5 |
+--------------------+------+