Posted by Dan Guzman on 02/08/07 12:56

To add on to Erland's response, OPTION (RECOMPILE) is a quick way to get a
better plan but it's not optimal in this case because there are 2 mutually
exclusive results in the same query. IMHO, the best approach in this case
is to formulate the query differently using an IF or UNION ALL.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"serge" <sergea@nospam.ehmail.com> wrote in message
news:oAyyh.94955$vT5.1877048@wagner.videotron.net...
> 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
>
>
>
>

[Back to original message]