I have a few tables that contain a column called "Order", which is used to sort by when retrieving the data. The purpose is to keep the data in a certain order when displayed to the end user.

The problem is that SQL Server puts null values above non-null values when doing an "order by". To reverse this behavior, this was the most elegant and efficient solution that I found:

Select FooValue
From foos
Order by (Case When [Order] Is Null Then 1 Else 0 End), [Order]

I found information about the original problem here, and the solution was from Tim in the comments. Thanks!