Nulls first/last in order by

Hi,

Another topic I'd like to give my take on is the lack of nulls first/last options (or their alternative) in SQL Server. Although it may not seem to be a big deal but it's irritating nevertheless.

Let's see how default order by works in Oracle:

select * from (
    select 2 as x from dual union all
    select 4 as x from dual union all
    select 1 as x from dual union all
    select 2 as x from dual union all
    select cast(null as number) as x from dual 
)
order by x;

         X
----------
         1
         2
         2
         4
      null

How about the same query in SQL Server? Here we go:

select x from (
    select 2 as x union all
    select 4 as x union all
    select 1 as x union all
    select 2 as x union all
    select cast(null as int) as x
) dataset
order by x;

x
-----------
NULL
1
2
2
4

Oh... we can already see one major difference! SQL Server puts null values above non null. Is it good or bad? Who knows. So, what would we do in Oracle if we wanted to see it sorted the other way?
Simple, we would add nulls first modifier to the order by statement, like so:

select x from (
    select 2 as x from dual union all
    select 4 as x from dual union all
    select 1 as x from dual union all
    select 2 as x from dual union all
    select cast(null as number) as x from dual 
)
order by x nulls first;

that results in:
         X
----------
      null
         1
         2
         2
         4

Since SQL Server does not allow us to do pretty much anything in a simple way, we need to be a little creative here, but we're not going to use answers found on the internet that encourage us to use simple isnull or case/when over the sorted column, oh no. If we did, we would have to make sure that whatever value we chose to represent null in the sort is largest/smallest that anything else in the data. I'm sure most of us would quickly jump that we know data etc but... what if you're sorting on nvarchar or developing software that should be collation independent? We NEED to sort on expression and use it before the column we're sorting on. Here's the only sensible solution:

select x from (
    select 2 as x union all
    select 4 as x union all
    select 1 as x union all
    select 2 as x union all
    select cast(null as int) as x
) dataset
order by case when x is null then 1 else 0 end, x;

x
-----------
1
2
2
4
NULL

Just as a note - in both MS SQL and Oracle nulls follow asc and desc keywords moving them to the beginning or the end of list. Therefore all the examples above would be reversed when paired with desc keyword.

Voila!

Best