ORDER BY in SQL Server vs Postgresql

Question

I am trying to run ORDER BY on a huge data set and produce dense rank values to extract the distinct number of rows based on the dense rank.Later, I am using the dense rank value as a surrogate key in my entire process to carry forward the intermediate results and use it further for some performance testing.

Issue I am facing :-

The problem I am having is I migrated the data set from Matrix(postgresql)(PRODUCTION environment) to SQL Server(New environment ) and implemented the dense rank function. But because of having different dense rank results which is due to the ORDER BY clause behavior in SQL Server and Matrix I am not able to proceed further in validating 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) output :-

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

Can someone tell me why there is a difference in the behavior of ORDER BY between two different database environments? Any help would be appreciated. Thanks.

Answer 1

That is caused by a different sorting collation. Try using 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;

returns

+--------------------+------+
|        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;

returns

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