Confuso sobre o comportamento da variável na instrução WHERE quando o valor é nulo

Considere esta consulta:

select count(*)
from my_test_table t
where null is null;

Faz sentido o otimizador fazer uma busca lá? Não há nada contra o que procurar. Tentar forçá-lo com uma dica gera um erro:

select count(*)
from my_test_table t with (forceseek)
where null is null;

Msg 8622, Nível 16, Estado 1, Linha 15

O processador de consultas não pôde produzir um plano de consulta devido às dicas definidas nesta consulta. Reenvie a consulta sem especificar nenhuma dica e sem usar SET FORCEPLAN.

Para a consulta que você tem agora, você pode querer uma busca de índice dependendo do valor do parâmetro. No entanto, uma busca de índice não é válida para todos os valores de parâmetro possíveis, portanto, seu plano armazenado em cache não pode conter uma busca. Além disso, quando o otimizador de consulta cria o plano, ele não verifica primeiro o valor da variável local.

Você indicou que a consulta é complexa, então provavelmente a melhor solução será atualizar para pelo menos o SQL Server 2008 R2 SP2 para que você possa aproveitar a Otimização de Incorporação de Parâmetros (PEO) . Isso combinado com uma OPTION (RECOMPILE)dica permite que o otimizador de consulta detecte o valor da variável local antes de executar o plano. O plano de consulta não pode ser reutilizado por outra consulta.

Vamos vê-lo em ação. O plano de consulta real para a consulta a seguir mostra uma busca de índice:

declare @productId int = 320;

select count(*)
from my_test_table t
where productId = @productId
or @productId is null
OPTION (RECOMPILE);

O plano de consulta real para o seguinte mostra uma verificação:

declare @productId int = NULL;

select count(*)
from my_test_table t
where productId = @productId
or @productId is null
OPTION (RECOMPILE);

Se a atualização não for uma opção, suponho que você possa tentar isso:

select *
from my_test_table t 
where productId = @productId
and @productId is not null

UNION ALL

select *
from my_test_table t
where @productId is null;

Isso pode fazer uma busca ou uma varredura dependendo do valor da variável local, mas pode ter efeitos colaterais não intencionais.

Se isso também não for aceitável, acho que sua única opção é usar o SQL dinâmico, que já foi abordado em outra resposta.

select count(*) from my_test_table t with(nolock) where productId = 320
or 320 is null

Como 320 is nullsempre retorna false , o otimizador é inteligente o suficiente para remover " or 320 is null"

Portanto, a afirmação acima é equivalente a

select count(*) from my_test_table t with(nolock) where productId = 320

productid é CI e também productid é índice seletivo o suficiente para que o otimizador decida indexar a busca.

select count(*) from my_test_table t with(nolock) where productId = @productId
or @productId is null

Suponha @productid=2 (inicialmente)

Mesmo assim, Estimated number of rows & Actual number of rows is always maximummesmo se você fornecer valor para a variável. Então, o otimizador decida indexar a varredura, sempre.

select count(*) from my_test_table t with(nolock) where productId = @productId
    or @productId is null
    OPTION (RECOMPILE);

aqui o plano mudará a cada vez. Então, quando @productid não for nulo, será Index Seekoutra verificação de índice.

Mas suponha que sua frequência de pesquisa seja muito alta, então a recompilação repetida aumentará a sobrecarga no servidor.

Na vida real, se sua consulta for realmente como acima, você poderá usar se mais

 if(@productid is not null)
    select count(*) from my_test_table t with(nolock) where productId = @productId

else 
SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'my_test_table' AND (index_id < 2)

Se a sua consulta de pesquisa for muito complexa também será usada com muita frequência, então você pode optar por sql dinâmico parametrizado usando "sp_executesql". Eu não acho que sp_executesql tenha qualquer limitação como 'Sql injection'. Em vez de superar o "risco de injeção de sql em sql dinâmico, devemos usar sp_executesql.

Você deve compartilhar sua consulta complexa em outro segmento.

O SQL Server tenta produzir um plano de execução de consulta reutilizável que pode ser executado de uma só vez quando você executa a mesma consulta novamente. Portanto, não pode usar o fato de @productId IS NULL, porque você executará a mesma consulta com @productId NOT NULL. Não sei se você pode impedir diretamente que isso seja considerado, embora possa controlar o cache do plano e a reutilização.

Eu abordo situações como essa usando "SQL dinâmico" em que o código SQL é construído em uma variável nvarchar(max) e depois executado a partir disso, com variáveis ​​como parâmetros se você usar sp_executesql. Esse método tem limitações e riscos ("injeção de SQL"), mas, nesse caso, você pode fazer com que a condição @productId apareça na consulta somente quando @productId não for NULL. Então, na verdade, duas consultas diferentes para os dois casos.

SET @workstring = N'SET @count = (SELECT COUNT(*) FROM my_test_table ' +
    CASE WHEN ( @productId IS NOT NULL ) THEN N'WHERE ( productId = @testThis )' 
         ELSE N'' END
EXEC sp_executesql @workstring, N'@count int OUTPUT, @testThis int',
    @count OUTPUT, @productId

Para instruções complexas mais longas, eu declaro um varchar(max) longo de SQL lindamente definido contendo marcadores de token, como neste caso @{where_condition}, então eu uso REPLACE() para alterá-los para o que o código realmente precisa ser, em neste caso, o resultado da instrução CASE. Posso verificar antes da execução se não há um '@{' restante na string!