NULL Sort Order: Put Missing Values Where Readers Expect

I make NULL sort order explicit when readers expect missing values at the bottom. SQL Server treats NULL as the lowest value for ordering. A small CASE bucket changes placement without inventing a score.

Read the default ascending order

The first query sorts by Score ascending and then ItemId. The two missing scores appear first because SQL Server orders NULL below known values. Ten follows, then the two scores of twenty.

That order is defined behavior, but it doesn’t automatically match a report’s purpose. I’d ask whether the reader needs incomplete records first or last. A default order is useful only when it communicates the intended priority of the displayed rows.

WITH Items AS
(
 SELECT ItemId, CAST(Score AS int) AS Score
 FROM (VALUES (1,20),(2,CAST(NULL AS int)),(3,10),
              (4,20),(5,CAST(NULL AS int))) v(ItemId,Score)
)
SELECT ItemId, Score
FROM Items
ORDER BY Score ASC, ItemId ASC;

WITH Items AS
(
 SELECT ItemId, CAST(Score AS int) AS Score
 FROM (VALUES (1,20),(2,CAST(NULL AS int)),(3,10),
              (4,20),(5,CAST(NULL AS int))) v(ItemId,Score)
)
SELECT ItemId, Score
FROM Items
ORDER BY CASE WHEN Score IS NULL THEN 1 ELSE 0 END,
         Score ASC, ItemId ASC;

Add an explicit missing-value bucket

The second query gives known scores bucket zero and missing scores bucket one. The bucket is the first ordering expression, so every known score precedes every missing score. The displayed Score values themselves stay unchanged.

I prefer that distinction to replacing NULL with an arbitrary large number. A replacement can collide with a legitimate score or change later calculations. A sort expression can arrange the presentation without pretending that an unknown business value has become known.

Keep the numeric direction separate

Score ASC remains the next ordering expression. Within the known-score bucket, ten comes before twenty. To reverse the numeric preference, that part of the ORDER BY needs a deliberate direction change.

The NULL bucket and the score direction answer different questions. I’d review them independently so a request for highest scores first doesn’t accidentally move missing scores to the top. The final output order should be stated in terms that a reader can verify.

Resolve ties with a stable key

ItemId orders the two scores of twenty and the two missing scores. It gives these supplied rows a deterministic sequence within their tied groups. Without it, the example wouldn’t specify which tied row appears first.

I’d use a genuinely unique ordering key in production. A convenient label can also contain ties, so it doesn’t always finish the ordering contract. Stable ordering matters for repeated exports and for readers comparing two versions of the same report.

Avoid hidden ordering assumptions

The sequence of VALUES inputs doesn’t establish the final result order. ORDER BY does that explicitly for both statements. A table’s physical arrangement or a previously observed plan isn’t a substitute for that clause.

I retain complete expected row sequences here, not just the set of values. All five rows exist in both results, but their order differs. A validation that checks only membership would miss the presentation rule this example is designed to establish.

Review presentation and access paths separately

This query reads only inline values and changes no data. It demonstrates a sorting contract, not an indexed access-path recommendation. A production query using CASE in ORDER BY needs its own plan and workload review.

I’d keep that performance review separate from the meaning of missing values. An efficient order can still tell the wrong story to a reader. Start with the intended row sequence, preserve the original values, then evaluate how the real workload produces that sequence.

Say where the missing rows go, and nobody has to guess.

A missing score is not a large score, it is a separate sort bucket.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.