Início / dba / Perguntas / 107876
Accepted
user2173353
Asked: 2015-07-24 04:06:27 +0800 CST

Melhor maneira de escrever uma consulta SQL que verifica uma coluna para um valor não NULL ou NULL

  • 772

Tenho um SP com um parâmetro que tem NULL como valor padrão e então quero fazer uma consulta assim:

SELECT ...
FROM ...
WHERE a.Blah = @Blah AND (a.VersionId = @VersionId OR (@VersionId IS NULL AND a.VersionId IS NULL));

O WHEREacima verifica um valor não NULL e um valor NULL para @VersionId.

Seria melhor em termos de desempenho usar uma IFinstrução e duplicar a consulta em uma que procura não NULL e outra para NULL assim? :

IF @VersionId IS NULL BEGIN
    SELECT ...
    FROM ...
    WHERE a.Blah = @Blah AND a.VersionId IS NULL;
ELSE BEGIN
    SELECT ...
    FROM ...
    WHERE a.Blah = @Blah AND a.VersionId = @VersionId;
END

Ou o otimizador de consulta torna essencialmente o mesmo?

ATUALIZAR:

(Nota: estou usando o SQL Server)

(E até onde eu sei, usar a.VersionId = @VersionIdpara ambos os casos não vai funcionar, vai?)

sql-server-2008 performance

1 respostas

  1. Best Answer

    Esse padrão

    column = @argument OR (@argument IS NULL AND column IS NULL)

    pode ser substituído por

    EXISTS (SELECT column INTERSECT SELECT @argument)

    Isso permitirá que você corresponda a um NULL com um NULL e permitirá que o mecanismo use um índice com columneficiência. Para uma excelente análise aprofundada dessa técnica, indico o artigo do blog de Paul White:

    Como existem dois argumentos em seu caso particular, você pode usar a mesma técnica de correspondência com @Blah– dessa forma, você poderá reescrever toda a cláusula WHERE de forma mais ou menos concisa:

    WHERE
  EXISTS (SELECT a.Blah, a.VersionId INTERSECT SELECT @Blah, @VersionId)

    Isso funcionará rapidamente com um índice em (a.Blah, a.VersionId).

    Ou o otimizador de consulta torna essencialmente o mesmo?

    Neste caso, sim. Em todas as versões (pelo menos) do SQL Server 2005 em diante, o otimizador pode reconhecer o padrão e substituí-lo pela comparação col = @var OR (@var IS NULL AND col IS NULL)adequada . ISIsso depende da correspondência de reescrita interna, portanto, pode haver casos mais complexos em que isso nem sempre é confiável.

    Nas versões do SQL Server a partir de 2008 SP1 CU5 inclusive , você também tem a opção de usar a Otimização de Incorporação de Parâmetros via OPTION (RECOMPILE), onde o valor de tempo de execução de qualquer parâmetro ou variável é inserido na consulta como um literal antes da compilação.

    Assim, pelo menos em grande parte, neste caso a escolha é uma questão de estilo, embora a INTERSECTconstrução seja inegavelmente compacta e elegante.

    Os exemplos a seguir mostram o 'mesmo' plano de execução para cada variação (literais versus referências de variáveis ​​excluídas):

    DECLARE @T AS table
(
    c1 integer NULL,
    c2 integer NULL,
    c3 integer NULL

    UNIQUE CLUSTERED (c1, c2)
);

-- Some data
INSERT @T
    (c1, c2, c3)
SELECT 1, 1, 1 UNION ALL
SELECT 2, 2, 2 UNION ALL
SELECT NULL, NULL, NULL UNION ALL
SELECT 3, 3, 3;

-- Filtering conditions
DECLARE 
    @c1 integer,
    @c2 integer;

SELECT
    @c1 = NULL,
    @c2 = NULL;

-- Writing the NULL-handling out explicitly
SELECT * 
FROM @T AS T
WHERE 
(
    T.c1 = @c1
    OR (@c1 IS NULL AND T.c1 IS NULL)
)
AND 
(
    T.c2 = @c2
    OR (@c2 IS NULL AND T.c2 IS NULL)
);

-- Using INTERSECT
SELECT * 
FROM @T AS T
WHERE EXISTS 
(
    SELECT T.c1, T.c2 
    INTERSECT 
    SELECT @c1, @c2
);

-- Using separate queries
IF @c1 IS NULL AND @c2 IS NULL
    SELECT * 
    FROM @T AS T
    WHERE T.c1 IS NULL
    AND T.c2 IS NULL
ELSE IF @c1 IS NULL
    SELECT * 
    FROM @T AS T
    WHERE T.c1 IS NULL
    AND T.c2 = @c2
ELSE IF @c2 IS NULL
    SELECT * 
    FROM @T AS T
    WHERE T.c1 = @c1
    AND T.c2 IS NULL
ELSE
    SELECT * 
    FROM @T AS T
    WHERE T.c1 = @c1
    AND T.c2 = @c2;

-- Using OPTION (RECOMPILE)
-- Requires 2008 SP1 CU5 or later
SELECT * 
FROM @T AS T
WHERE 
(
    T.c1 = @c1
    OR (@c1 IS NULL AND T.c1 IS NULL)
)
AND 
(
    T.c2 = @c2
    OR (@c2 IS NULL AND T.c2 IS NULL)
)
OPTION (RECOMPILE);
    • 38

relate perguntas