Is this expected behavior?

I posted this at the asp.net forums but somone suggested I post it here. So:

Try this in sql server:

select COALESCE(a1, char(254)) as c1 from

(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1

group by a1

with rollup order by c1

select COALESCE(a1, char(255)) as c1 from

(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1

group by a1

with rollup order by c1

The only difference is that the first one uses 254 and the second one uses 255. The first sorts like this:

W
Y
Ya
Z
t

The second one sorts like this:

W
Y
?
Ya
Z

Is this expected behavior?

It is because the sort order is based on the character set you are using, not where the characters show up in the ascii or unicode charts. If you want it to sort based on position like that, you need to use a binary sort order.

select COALESCE(a1, char(254)) COLLATE Latin1_General_BIN as c1
from (select 'Z' as a1
union select 'Ya' as a1
union select 'Y' as a1
union select 'W' as a1) as b1
group by a1
with rollup order by c1

select COALESCE(a1, char(255)) COLLATE Latin1_General_BIN as c1
from (select 'Z' as a1
union select 'Ya' as a1
union select 'Y' as a1
union select 'W' as a1) as b1
group by a1
with rollup order by c1

In this case both characters that are NULL will sort to the end of the results.