Como escrever uma consulta no SQL Server para encontrar os valores mais próximos

Supondo que a coluna esteja indexada, o seguinte deve ser razoavelmente eficiente.

Com duas buscas de 10 linhas e depois uma espécie de (até) 20 retornadas.

WITH CTE
     AS ((SELECT TOP 10 *
          FROM   YourTable
          WHERE  YourCol > 32
          ORDER  BY YourCol ASC)
         UNION ALL
         (SELECT TOP 10 *
          FROM   YourTable
          WHERE  YourCol <= 32
          ORDER  BY YourCol DESC))
SELECT TOP 10 *
FROM   CTE
ORDER  BY ABS(YourCol - 32) ASC 

(ou seja, potencialmente algo como o abaixo)

Ou outra possibilidade (que reduz o número de linhas classificadas para no máximo 10)

WITH A
     AS (SELECT TOP 10 *,
                       YourCol - 32 AS Diff
         FROM   YourTable
         WHERE  YourCol > 32
         ORDER  BY Diff ASC, YourCol ASC),
     B
     AS (SELECT TOP 10 *,
                       32 - YourCol AS Diff
         FROM   YourTable
         WHERE  YourCol <= 32
         ORDER  BY YourCol DESC),
     AB
     AS (SELECT *
         FROM   A
         UNION ALL
         SELECT *
         FROM   B)
SELECT TOP 10 *
FROM   AB
ORDER  BY Diff ASC

NB: O plano de execução acima foi para a definição de tabela simples

CREATE TABLE [dbo].[YourTable](
    [YourCol] [int] NOT NULL CONSTRAINT [SomeIndex] PRIMARY KEY CLUSTERED 
)

Tecnicamente, o Sort na ramificação inferior também não deve ser necessário, pois também é ordenado por Diff, e seria possível mesclar os dois resultados ordenados. Mas não consegui esse plano.

A query tem ORDER BY Diff ASC, YourCol ASCe não apenas ORDER BY YourCol ASC, pois foi isso que acabou dando certo para se livrar do Sort no branch superior do plano. Eu precisava adicionar a coluna secundária (mesmo que ela nunca mude o resultado, pois YourColserá o mesmo para todos os valores com o mesmo Diff) para que ela passe pela junção de mesclagem (concatenação) sem adicionar um Sort.

O SQL Server parece capaz de inferir que um índice em X procurado em ordem crescente entregará linhas ordenadas por X + Y e nenhuma classificação é necessária. Mas não é capaz de inferir que percorrer o índice em ordem decrescente entregará linhas na mesma ordem que YX (ou mesmo apenas unário menos X). Ambas as ramificações do plano usam um índice para evitar uma classificação, mas as TOP 10da ramificação inferior são classificadas por Diff(mesmo que já estejam nessa ordem) para colocá-las na ordem desejada para a mesclagem.

Para outras definições de consultas/tabelas, pode ser mais complicado ou não ser possível obter o plano de mesclagem com apenas uma espécie de uma ramificação - pois depende de encontrar uma expressão de ordenação que o SQL Server:

1. Aceita que a busca de índice fornecerá a ordem especificada para que nenhuma classificação seja necessária antes do início.
2. Fica feliz em usar na operação de mesclagem, portanto, não requer classificação após aTOP

Estou um pouco intrigado e surpreso que tenhamos que fazer União neste caso. A seguir é simples e mais eficiente

SELECT TOP (@top) *
FROM @YourTable
ORDER BY ABS(YourCol-@x)

A seguir está o código completo e o plano de execução comparando as duas consultas

DECLARE @YourTable TABLE (YourCol INT)
INSERT @YourTable (YourCol)
VALUES  (32),(11),(15),(123),(55),(54),(23),(43),(44),(44),(56),(23)

DECLARE @x INT = 100, @top INT = 5

--SELECT TOP 100 * FROM @YourTable
SELECT TOP (@top) *
FROM @YourTable
ORDER BY ABS(YourCol-@x)

;WITH CTE
     AS ((SELECT TOP 10 *
          FROM   @YourTable
          WHERE  YourCol > 32
          ORDER  BY YourCol ASC)
         UNION ALL
         (SELECT TOP 10 *
          FROM   @YourTable
          WHERE  YourCol <= 32
          ORDER  BY YourCol DESC))
SELECT TOP 10 *
FROM   CTE
ORDER  BY ABS(YourCol - 32) ASC 

Refinamento da segunda sugestão de Martin:

WITH AB
     AS (SELECT *, ABS(32 - YourCol) AS Offset
         FROM   YourTable),
SELECT TOP 10 *
FROM   AB
ORDER  BY Offset ASC