Case TSQL statement in WHERE clause causing scanning (ss2005)

WHEN @IncludeFreight='Y' THEN 1
WHEN (ItemNumber NOT LIKE '9898%') AND (ItemNumber <> 'FREIGHT') THEN 1

The above is an snippet from a “catch all” query, I uncovered it in one of our reporting SQL statements it, was declared “WITH RECOMPILE”  as it should, on order to properly optomise, but the query was refusing to finish executing on the 4.5million rows in the table involved. It lots of IO wait states on our table.

It seems that the query optomiser just couldn’t handle the case in the where clause with parameters.

Changed it to the following and it executed in seconds.

AND ( @IncludeFreight='Y'
OR (@IncludeFreight='N' AND ItemNumber != 'FREIGHT' AND ItemNumber NOT LIKE '9898%'))

Most of the theory behind this behaviour can be seen in this article: