How does order by zero work

In the following script (see a full demo here):

SELECT *
FROM Table1
ORDER BY 
   CASE WHEN Field1 IS NOT NULL 
     THEN 0 
   END

• What is the type of the 0?

• How 0 is interpreted? It's not the ordinal number of the columns in the select list, because the select list enumeration begins with 1, not 0. So what is it?

To elaborate a bit, compare the above snippet with the following, which emits an error:

SELECT *
FROM Table1
ORDER BY 0

The ORDER BY position number 0 is out of range of the number of items in the select list.

And also compare it with the following snippet, which emits another error:

SELECT *
FROM Table1
ORDER BY '0'

A constant expression was encountered in the ORDER BY list, position 1.

It seems that nesting the 0 value under a case clause makes it work, but I don't understand why and how?

• This question is somewhat a follow-up to this old one, which doesn't explain why it works compared to the alternatives I listed above:

How does work SQL with order by 0?

The ORDER BY Clause documentation states:

order_by_expression

Specifies a column or expression on which to sort the query result
set. A sort column can be specified as a name or column alias, or a
nonnegative integer representing the position of the column in the
select list.

The doc could arguably state this more clearly. Per the second sentence, "column" can be specified as:

• column name

• column alias

• column position

CASE is an "expression" rather than a column specification. The expression does not specify a sort order column. The value of expression result is used for sorting.

The result of the expression CASE WHEN Field1 IS NOT NULL THEN 0 END is an integer (due to the zero integer constant) with a value of zero or NULL. The expression type information can be gleaned with sys.dm_exec_describe_first_result_set:

SELECT system_type_name, is_nullable
FROM sys.dm_exec_describe_first_result_set(N'SELECT CASE WHEN Field1 IS NOT NULL THEN 0 END FROM dbo.Table1',NULL,NULL);

+------------------+-------------+
| system_type_name | is_nullable |
+------------------+-------------+
| int              |           1 |
+------------------+-------------+

Using slightly modified data from your fiddle with and a valid CASE expression, you can see that rows with NULL values sort before those with non-null values because the CASE expression returned a NULL for rows with a NULL Field1 value and a zero for rows with non-NULL values.

CREATE TABLE Table1(ID INT IDENTITY(1,1), Field1 INT, FieldSomethingElse VARCHAR(10));
INSERT INTO Table1 (Field1, FieldSomethingElse) VALUES (1, 'some');
INSERT INTO Table1 (Field1, FieldSomethingElse) VALUES (765, 'or');
INSERT INTO Table1 (Field1, FieldSomethingElse) VALUES (2, 'thing');
INSERT INTO Table1 (Field1, FieldSomethingElse) VALUES (NULL, 'another');
INSERT INTO Table1 (Field1, FieldSomethingElse) VALUES (NULL, 'thing');

SELECT *,CASE WHEN Field1 IS NOT NULL THEN 0 END AS ValueUsedForSorting
FROM Table1
ORDER BY 
   CASE WHEN Field1 IS NOT NULL 
     THEN 0 
   END;

+----+--------+--------------------+---------------------+
| ID | Field1 | FieldSomethingElse | ValueUsedForSorting |
+----+--------+--------------------+---------------------+
|  4 | NULL   | another            | NULL                |
|  5 | NULL   | thing              | NULL                |
|  1 | 1      | some               | 0                   |
|  3 | 765    | or                 | 0                   |
|  2 | 2      | thing              | 0                   |
+----+--------+--------------------+---------------------+

I'll add that since NULLs sort before non-NULL values anyway, one could simply order by Field1 to provide the desired sorting behavior and perhaps leverage an index on the column too. Unlike the CASE expression, this method will also sort non-NULL values according the Field1 value rather than the undefined order of the CASE expression zero values (due to ties of zero values).

SELECT *,Field1 AS ValueUsedForSorting
FROM Table1
ORDER BY Field1;

+----+--------+--------------------+---------------------+
| ID | Field1 | FieldSomethingElse | ValueUsedForSorting |
+----+--------+--------------------+---------------------+
|  4 | NULL   | another            | NULL                |
|  5 | NULL   | thing              | NULL                |
|  1 | 1      | some               | 1                   |
|  3 | 2      | thing              | 2                   |
|  2 | 765    | or                 | 765                 |
+----+--------+--------------------+---------------------+

StackExchange Database Administrators Q#305155, answer score: 8