Confused about behaviour of variable in WHERE statement when value is null I have a table like (SQLServer 2008):

CREATE TABLE [dbo].[my_test_table](
    [productId] [int] NOT NULL,
    [purchaseId] [bigint] NOT NULL,
    (some other columns....),
 CONSTRAINT [PK_my_test_table] PRIMARY KEY CLUSTERED 
(
    [productId] ASC,
    [purchaseId] ASC
))


having around 10 milions rows.

I want a query that returns the total number of rows for a product or, if the product is not secified, the total number of rows for all the products.
Something like:

declare @productId int

set @productId = 320

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


The problem is that query takes much more time than the equivalent query:

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


How can we explain this behaviour ?

Here are the execution plans:
Consider this query:

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


Does it make sense for the optimizer to do a seek there? There's nothing to seek against. Trying to force it with a hint throws an error:

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



  Msg 8622, Level 16, State 1, Line 15
  
  Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.


For the query that you have right now you may want an index seek depending on the parameter value. However, an index seek is not valid for all possible parameter values so your cached plan cannot contain a seek. In addition, when the query optimizer builds the plan it does not check the value of the local variable first.

You indicated that the query is complex so likely the best solution will be to upgrade to at least SQL Server 2008 R2 SP2 so you can take advantage of the Parameter Embedding Optimization (PEO). That combined with an OPTION (RECOMPILE) hint allows the query optimizer to sniff the value of the local variable before executing the plan. The query plan cannot be reused by another query. 

Let's see it in action. The actual query plan for the following query shows an index seek:

declare @productId int = 320;

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




The actual query plan for the following shows a scan:

declare @productId int = NULL;

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




If upgrading isn't an option I suppose you could try this:

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;


That can will do a seek or a scan depending on the value of the local variable but could have unintended side effects.

If that also isn't acceptable I think your only option is to use dynamic SQL which was already covered in another answer.