Because SQL Server follows ANSI standard string comparison rules, so 'a' and 'a ' are equal. eg

SELECT *, rank() over (order by text) rank 
FROM (VALUES
    (1, 'a' + CHAR(33)),
    (2, 'a   '),
    (3, 'a  '),
    (4, 'a '),
    (5, 'a' + CHAR(31)),
    (6, 'a')
)v(rownum, text)
ORDER BY text

outputs

rownum      text rank
----------- ---- --------------------
5           a   1
3           a    2
4           a    2
2           a    2
6           a    2
1           a!   6

(6 rows affected)

And the order in which rows are returned that are equal in the ORDER BY clause is undefined, and plan-dependent.

So it seems as if it treats 'a'+(no spaces) and 'a'+(any amount of spaces) as if they were all equivalent.

This is exactly what is going on. When dealing with equality operations SQL Server effectively ignores trailing spaces, so '' and ' ' are seen as equal, and so too are 'a' , 'a ' and 'a ' .

As a result the ORDER BY you have, ORDER BY text , isn't deterministic; you have 4 rows that all have the same value. This means that when you run the query the 4 rows with the same value are returned in an arbitrary order. If you wanted to ensure you ordered values with the least trailing spaces first then you could add DATALENGTH(text) to the ORDER BY , as unlike LEN it does include trailing spaces in its calculation:

SELECT rownum,
       text,
       DATALENGTH(text) AS Characters
FROM (VALUES(1, 'a' + CHAR(33)),
            (2, 'a   '),
            (3, 'a  '),
            (4, 'a '),
            (5, 'a' + CHAR(31)),
            (6, 'a'))v(rownum, text)
ORDER BY text,
         DATALENGTH(text);

To get the order you were expecting, you'd want to order the data by it's binary value:

SELECT rownum,
       text,
       DATALENGTH(text) AS Characters,
       CONVERT(varbinary(4),text)
FROM (VALUES(1, 'a' + CHAR(33)),
            (2, 'a   '),
            (3, 'a  '),
            (4, 'a '),
            (5, 'a' + CHAR(31)),
            (6, 'a'))v(rownum, text)
ORDER BY CONVERT(varbinary(4),text);