Why Optimizer Is Not Smart? Is Dynamic SQL My Only Option...

declare @ContactId as integer

set @ContactId = 5

select *

from Person.Contact

where ContactId = @ContactId

OR @ContactId = -1



If you run this in SQL 2005 on the AdventureWorks database,

why the logical reads is 561

Table 'Contact'. Scan count 1, logical reads 56



and not 2 when you run without the second OR condition:

declare @ContactId as integer

set @ContactId = 5

select *

from Person.Contact

where ContactId = @ContactId



How can i use the same SP and either get one record returned

by passing the ID of the field, or pass a dummy parameter like

-1 in order to get ALL the records returned.

In this case even when i pass a parameter like ContactID = 5

there is still a table scan (clustered index scan in this case)

happening for the other OR condition.

There's no method to tell SQL to start checking the first condition

whether or not it is true then if it is false then check the second OR

conditon. On the same topic does this mean all OR conditions are

ALWAYS verified regardless if one of them has already been determined

to be True?



Thank you