tsql - SQL SERVER - Understanding how MIN(text) works

Question

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!

Answer 1

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');