Force SQL Server to run query conditions as written?

I'm using SQL Server 2008 R2 and I have this pseudo query (SP) :

select ...
from ...
WHERE    @LinkMode IS NULL
     AND (myColumn IN (...very long-running query...))
     ...
     ...

The problem is that the query takes a very long time to execute -- even if I execute the SP with @LinkMode=2.

As you noticed, the long-running query should be executed only if @LinkMode is null, which is not the case here. In my case @LinkMode = 2 !

However, if I change it to :

select ...
    from ...
    WHERE    1=2
         AND (myColumn IN (...very long time exeted query...))
     ...
     ...

the SP does run fast.

I've heard before that sometimes the optimizer can optimize the order of criteria.

So I ask :

-
Even if the optimizer chooses a different route, what can be faster than checking if =null? I mean, I think that checking if a==null is much faster than running the other long query...

-
How can I force SQL Server to run the query as I've written it (the same order)?

You are falling into the "Catch-All Query" trap, which is explained very well by Gail Shaw here.

To summarize the problem: SQL Server optimizes away the significant overhead of query compilation by caching a query plan after compilation, and then later checking the cache for a matching query plan before a later compilation. The "matching" that occurs here is purely textual, so the actual value of a variable will not affect this.

That's good 99% of the time, but in some cases it's bad. One case where it's bad is when someone tries to construct a WHERE clause as though its like a short-circuiting IF statement in C, etc. This doesn't work well, because the SQL compiler has to make one query plan that will work regardless of what the parameter values actually are, and the only way that it can handle these "clever" logical switching-conditions in the WHERE clause is to make a simple brute-force plan that just scans the whole table, filtering the rows as it goes, without leveraging any indexes.

Not suprisingly, this makes them uniformly slow, no matter what the parameter/variable values are.

StackExchange Database Administrators Q#41343, answer score: 23