The NULLS LAST option for the ORDER BY clause has been an ANSI standard for a long time. Obviously, it does not mean that all database vendors have implemented it. Oh, no. Nothing like that. If you are looking for it in SQL Server or MySQL, you are doomed to failure. Only developers that use PostgreSQL and Oracle are lucky in this area. Those two databases support ORDER BY with NULLS FIRST/LAST option. Fortunately, fans of Microsoft still can change NULLs priority in ordering but they need to use some tricks to achieve that. In this article, I describe those tricks. Read more to find alternatives to NULLS LAST option in SQL Server.

Sometimes it happens that I need to retrieve rows from a database in a particular order. Nothing special, right? A number 3 is lower than a number 5. A text abc is lower then a text klmn. There seem to be nothing special about it, except only a few subjects - one of them is sorting NULL values. Is NULL lower or greater then a number 3, a text klmn and a date 2015-03-14?

The answer is - SQL Server treats NULL values as the lowest values. For example when sorted in ascending order, NULLs come first.

SELECT value
FROM test
ORDER BY value ASC

It is the same for all sortable data types: numbers, dates etc. 

Real life scenario of changing NULLs priority

What if you want to change the default NULL priority? Yes, there are some real life scenarios when you need that. An example? Sure. Here it is.

Imagine a banking system that takes care of credit cards, their limits etc. There is a table with each credit card and its monthly limit:

CREATE TABLE creditCard (
	id INT IDENTITY(1, 1) NOT NULL,
	monthlyLimit MONEY NULL
);

There are four cards:

INSERT INTO creditCard (monthlyLimit)
VALUES (1000), (5000), (10000), (NULL);

The first one has a limit to $1000 per month, the second one $5k, the third one $10k, the last one is a golden card without a limit. In this case NULL means infinity.

Now, I need to write a query that gets me cards with the highest monthly limit. The first thought is to use the following query:

SELECT *
FROM creditCard
ORDER BY monthlyLimit DESC

Obviously, the result is not correct. The fourth card has the highest limit (infinity) but it was wrongly returned last.

In Oracle or PostgreSQL you could write:

SELECT *
FROM creditCard
ORDER BY monthlyLimit DESC NULLS LAST

Workaround for NULLS LAST in SQL Server

Unfortunately, even the newest SQL Server release does not support the NULLS LAST option for the ORDER BY clause. So how can it be worked around in SQL Server?

There are a few ways but most of them have some disadvantages or simply do not work in all cases. I think the most popular and preferred by myself is adding a calculated column to the ORDER BY clause:

SELECT *
FROM creditCard
ORDER BY CASE WHEN monthlyLimit IS NULL THEN 1 ELSE 0 END DESC, monthlyLimit DESC;

An expected result is achieved but is it optimized for performance? Lets check execution plans of both queries (the one without the CASE column and with). Before generating the below execution plans, I added more rows to the table and I added a limit of returned rows (TOP 100). Now, there are about 17 000 rows to better reflect reality.

SELECT TOP 100 *
FROM creditCard
ORDER BY monthlyLimit DESC;

SELECT TOP 100 *
FROM creditCard
ORDER BY CASE WHEN monthlyLimit IS NULL THEN 1 ELSE 0 END DESC, monthlyLimit DESC;

As you can see, the query with CASE WHEN has a Compute Scalar operation in addition to the basic query. In total, it does not seem to affect performance as it has 0% query cost overhead to the total cost.

What if I wanted to optimize it and I did it by index creation on the monthlyLimit column?

CREATE INDEX IX_creditCard_monthlyLimit ON creditCard (monthlyLimit);

Execution plans after the index creation are shown below:

It shows that changing NULLs priority in sorting can significantly negatively affect performance.

Unfortunately, there is no better way to do this. If you really need to change NULLs priority, do it this way and live with the negative impact.

Ultimate solution

Of course, in the relational theory, NULL value means unknown, not infinity. Using it as infinity, no matter negative or positive, is a bad practice. If you need to set infinity in a numeric column, use a very big number. Big enough to be greater than any other value in this column set now or in the future.

Using the maximum allowed value for the data type might be a good idea. Then, you will avoid problems related to interpreting NULL values and a need of using tricks to simulate NULLS LAST/FIRST option.

If for at least a moment you regretted to use SQL Server, remember that there are many differences between SQL Server and Oracle DB. In some cases in favor of the first one, in some of the second one. To give an example in opposite to the NULLS LAST, filtered index exist in SQL Server, but does not in Oracle.