Search code examples
sql-servert-sqlsqltransaction

SQL SERVER - Understanding how MIN(text) works

I'm doing a little digging and looking for a explanation on how SQL server evaluates MIN(Varchar).

I found this remark in BOL: MIN finds the lowest value in the collating sequence defined in the underlying database

So if I have a table that has one row with the following values: 

Data

AA
AB
AC

Doing a SELECT MIN(DATA) would return back AA. I just want to understand the why behind this and understand the BOL a little better.

Thanks!

Solution

  • It's determined by the collation (sort order). For most cultures the collation order is the same as the alphabetical order in the English alphabet so:

    • 'AA' < 'AB'
    • 'AA' < 'AC'
    • 'AB' < 'AC'

    Therefore 'AA' is the minimum value. For other cultures this may not hold. For example a Danish collation would return 'AB' as the minimum because 'AA' > 'AB'. This is because 'AA' is treated as equivalent to 'Å' which is the last letter in the Danish alphabet. 

    SELECT MIN(s COLLATE Danish_Norwegian_CI_AS) FROM table1;

min_s
AB

    To get an "ordinary" sort order use the Latin1_General_Bin collation:

    SELECT MIN(s COLLATE Latin1_General_Bin) FROM table1;

min_s
AA

    To reproduce this result you can create this test table:

    CREATE TABLE table1 (s varchar(100));
INSERT INTO table1 (s) VALUES ('AA'), ('AB'), ('AC');