Como funciona o pedido por zero

A documentação da cláusula ORDER BY afirma:

ordem_por_expressão

Especifica uma coluna ou expressão na qual classificar o conjunto de resultados da consulta. Uma coluna de classificação pode ser especificada como um nome ou alias de coluna ou um número inteiro não negativo que representa a posição da coluna na lista de seleção.

O doc poderia, sem dúvida, afirmar isso mais claramente. De acordo com a segunda frase, "coluna" pode ser especificada como:

• nome da coluna
• alias de coluna
• posição da coluna

CASEé uma "expressão" em vez de uma especificação de coluna. A expressão não especifica uma coluna de ordem de classificação. O valor da expressão result é usado para classificação.

O resultado da expressão CASE WHEN Field1 IS NOT NULL THEN 0 ENDé um inteiro (devido à constante de inteiro zero) com valor zero ou NULL. As informações do tipo de expressão podem ser obtidas com 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 |
+------------------+-------------+

Usando dados ligeiramente modificados de seu violino e uma expressão CASE válida, você pode ver que as linhas com valores NULL são classificadas antes daquelas com valores não nulos porque a expressão CASE retornou um NULL para linhas com um valor NULL Field1 e um zero para linhas com valores não NULL.

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                   |
+----+--------+--------------------+---------------------+

Acrescentarei que, como NULLs classificam antes de valores não NULL, pode-se simplesmente ordenar por Field1 para fornecer o comportamento de classificação desejado e talvez alavancar um índice na coluna também. Ao contrário da expressão CASE, este método também classificará valores não NULL de acordo com o valor Field1 em vez da ordem indefinida dos valores zero da expressão CASE (devido aos laços de valores zero).

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                 |
+----+--------+--------------------+---------------------+

Quando você usa uma expressão (por exemplo CASE, ), ela não está mais classificando por nome de coluna ou pelo ordinal da coluna, mas agora por valor . Esse valor que você escolheu é 0 neste caso (apenas para Field1linhas não nulas), mas poderia ter sido 1, 100, 'Red' ou até FieldSomethingElse.

Se você construísse sua CASEexpressão um pouco mais, você veria o que quero dizer:

SELECT *
FROM Table1
ORDER BY 
   CASE 
       WHEN Field1 > 10 
           THEN 100 
        WHEN Field1 > 5
            THEN 200
        WHEN Field1 IS NOT NULL
            THEN 300
        ELSE 900
   END

O acima classifica as linhas semi-descendentes com base se o valor de Field1for maior que um determinado valor. Ele também tem um caso padrão, a ELSEcláusula, que colocará todas as linhas onde Field1 IS NULLno final dos resultados.

Obviamente, não há colunas com um nome ou com um ordinal de 100, 200, 300 ou 900, mas a CASEexpressão acima funciona porque o SQL Server classifica as linhas pelos valores na THENcláusula para as linhas que atendem a essa WHENcláusula.

A mesma coisa está acontecendo em sua CASEcláusula de exemplo quando você usa 0, está apenas classificando linhas não nulas no valor constante 0. Isso obviamente não significa muito em termos de classificação, exceto que todas as linhas não nulas serão classificadas juntos (já que todos serão classificados no mesmo valor de 0 e, portanto, sua ordem nessa classificação será não determinística) e todas as linhas nulas também serão classificadas juntas.

A instrução de pedido gera um 'campo virtual' que contém NULL ou 0 com base na condição do caso.

O ORDER BY é aplicado com base nesse campo virtual.

O valor zero na ordem por precede o valor NULL. Isso é tudo, é uma maneira de definir NULLs do campo1 no final da lista de pedidos.

Tente visualizá-lo desta forma:

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