I have a column in my database (a flag) with type varchar(1) that is populated either Y or NULL (not in my control).
In SQL Server, doing an ascending order by query, NULL is ordered at the top.
Is this SQL Server behaviour consistent with Oracle and DB2?
If, instead, I have a COALESCE on the column to ensure it is not null in the query, what performance issues (due to table scans and the like) could I hit?
The query needs to be consistent over all 3 databases, otherwise I will have to handle it in code, hence my thinking of using the COALESCE function.
I know for a fact that DB2 Express and DB2 (at least up to v8) does not support the NULLS FIRST clause.
If you want a portable solution, you may have to opt for something like:
select * from tbl where fld is null
union all select * from tbl where fld is not null
I think the result of the union (at least in DB2, you'll need to check the others) is guaranteed to be ordered correctly.
The coalesce will have performance implications since you're running a function for every row returned. However, it depends on the number of rows in the database.
You may well have to resort to doing two queries in code into two different record sets, then processing them in order.
EDIT: I've just checked the SQL standard and it's not guaranteed that the queries joined with a UNION ALL are sequenced; they might be inter-mixed. So it looks like you may have to resort to code running two different queries as mentioned above.