This one got me the other day. What would you expect the following to return?
SELECT 'X' AS line
UNION
SELECT 'X ' AS line
Notice the space in the second SELECT.
Well apparently SQL 2000 and 2005 both return 1 result. Even though its a UNION (and not a UNION ALL).
There is nothing I can see in Books on line about this. Why does it happen? I'm guessing it's a bug.
EDIT it's NOT a bug. See my follow up to this post for the reason why.
