Is this a Bug in the ORDER BY? The UNION behaves as expected, whereas the UNION ALL does not. I know UNION has an additional step where it gets rid of duplicates; but I can't understand why they sort differently. Does anyone have an explanation for this? -- UNION ALL ORDER BY Bug SELECT SPACE(1) AS [sp], 1 UNION ALL SELECT SPACE(2), 2 UNION ALL SELECT SPACE(5), 5 UNION ALL SELECT SPACE(4), 4 UNION ALL SELECT SPACE(0), 0 ORDER BY [sp] SELECT SPACE(1) AS [sp], 1 UNION SELECT SPACE(2), 2 UNION SELECT SPACE(5), 5 UNION SELECT SPACE(4), 4 UNION SELECT SPACE(0), 0 ORDER BY [sp] ![alt text] : /storage/temp/2163-union-all-order-by-bug.jpg
I seem to recall that SQL Server ignores trailing spaces for the purposes of most comparison operations, i.e. 'abc' = 'abc '. If you check the execution plan, this holds out - no sort is performed. SQL recognises that each column 1 value is effectively the same for comparision purposes and so just runs a constant scan. I suspect that the sort order for the union query is being enforced by having to merge join each item.
SPACE() returns varchar, and varchar ignores trailing spaces for comparison purposes (as @iainrobertson correctly states) select len(space(10)) -- => returns 0 so the rows all have the 'same' value in column `sp`. Since no order is specified beyond this column, then the rows can, and will be returned in any order.