Spool de varredura constante

Por que o otimizador introduz o spool aqui, qual é a razão para fazê-lo? Não há nada complexo além do carretel.

A coisa além do spool não é uma simples referência de tabela, que poderia simplesmente ser duplicada quando a alternativa de junção esquerda / anti semijunção é gerada.

Pode parecer um pouco com uma tabela (Constant Scan), mas para o otimizador* é uma UNION ALLdas linhas separadas na VALUEScláusula.

A complexidade adicional é suficiente para o otimizador optar por fazer o spool e reproduzir as linhas de origem e não substituir o spool por um simples "obter tabela" posteriormente. Por exemplo, a transformação inicial da junção completa se parece com isso:

Observe os carretéis extras introduzidos pela transformação geral. Os carretéis acima de uma mesa simples são limpos posteriormente pela regra SpoolGetToGet.

Se o otimizador tivesse uma SpoolConstGetToConstGetregra correspondente, ele poderia funcionar como você deseja, em princípio.

Como se livrar dele neste caso, quais são as maneiras possíveis?

Use uma tabela real (temporária ou variável) ou escreva a transformação da junção completa manualmente, por exemplo:

WITH 
    p([Id], [Code]) AS
    (
        SELECT @id1, 'A'
        UNION ALL
        SELECT @id2, 'B'
    ),
    FullJoin AS
    (
        SELECT
            p.Code,
            d.[Status]
        FROM p
        LEFT JOIN #data d 
            ON d.[Id] = p.[Id]
        UNION ALL
        SELECT
            NULL,
            D.[Status]
        FROM #data AS D
        WHERE NOT EXISTS
        (
            SELECT *
            FROM p
            WHERE p.Id = D.Id
        )
    )
SELECT
    COALESCE(FullJoin.Code, 'X') AS Code,
    COALESCE(FullJoin.Status, 0) AS [Status]
FROM FullJoin;

Plano para reescrita manual:

Este tem um custo estimado de 0,0067201 unidades, em comparação com 0,0203412 unidades do original.

* Pode ser observado como a LogOp_UnionAllna Árvore Convertida (TF 8605). Na Árvore de Entrada (TF 8606) é um arquivo LogOp_ConstTableGet. A Árvore Convertida mostra a árvore de elementos de expressão do otimizador após análise, normalização, algebrização, associação e algum outro trabalho preparatório. A Árvore de entrada mostra os elementos após a conversão para a forma normal de negação (conversão NNF), colapso da constante de tempo de execução e alguns outros bits e bobs. O NNF convert inclui lógica para recolher uniões lógicas e tabelas comuns, entre outras coisas.

O carretel de tabela está simplesmente criando uma tabela a partir dos dois conjuntos de tuplas presentes na VALUEScláusula.

Você pode eliminar o spool inserindo esses valores em uma tabela temporária primeiro, assim:

DROP TABLE IF EXISTS #data;
CREATE TABLE #data ([Id] int, [Status] int);

INSERT INTO #data
VALUES (100, 1), (101, 2), (102, 3), (103, 2);

DROP TABLE IF EXISTS #p;
CREATE TABLE #p
(
    Id int NOT NULL
    , Code char(1) NOT NULL
);

DECLARE @id1 int = 101, @id2 int = 105;

INSERT INTO #p (Id, Code)
VALUES
        (@id1, 'A'),
        (@id2, 'B');


SELECT
    COALESCE(p.[Code], 'X') AS [Code],
    COALESCE(d.[Status], 0) AS [Status]
FROM #p p
    FULL JOIN #data d ON d.[Id] = p.[Id];

Observando o plano de execução de sua consulta, vemos que a lista de saída contém duas colunas que usam o Unionprefixo; esta é uma dica de que o spool está criando uma tabela de uma fonte de união:

O FULL OUTER JOINSQL Server requer que o SQL Server acesse os valores pduas vezes, uma para cada "lado" da junção. A criação de um spool permite que os loops internos resultantes se unam para acessar os dados em spool.

Curiosamente, se você substituir o FULL OUTER JOINpor a LEFT JOINe a RIGHT JOINe UNIONos resultados juntos, o SQL Server não usará um spool.

SELECT
    COALESCE(p.[Code], 'X') AS [Code],
    COALESCE(d.[Status], 0) AS [Status]
FROM (VALUES
        (101, 'A'),
        (105, 'B')
    ) p([Id], [Code])
    LEFT JOIN #data d ON d.[Id] = p.[Id]
UNION
SELECT
    COALESCE(p.[Code], 'X') AS [Code],
    COALESCE(d.[Status], 0) AS [Status]
FROM (VALUES
        (101, 'A'),
        (105, 'B')
    ) p([Id], [Code])
    RIGHT JOIN #data d ON d.[Id] = p.[Id];

Observe que não estou sugerindo o uso da UNIONconsulta acima; para conjuntos maiores de entrada, pode não ser mais eficiente do que o simples FULL OUTER JOINque você já tem.