|
Posted by serge on 02/08/07 05:30
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
Navigation:
[Reply to this message]
|