|
|
Posted by Erland Sommarskog on 09/25/04 11:49
Tony Rogerson (tonyrogerson@sqlserverfaq.com) writes:
>> SELECT ...
>> FROM tbl
>> WHERE (key1 = @key1 AND @key1 IS NOT NULL)
>> OR (key2 = @key2 AND @key2 IS NOT NULL)
>> OR (key3 = @key3 AND @key3 IS NOT NULL)
>
> Is that not just because they are OR's?
Not only. With the IS NOT NULL there is no startup filter, so the indexes
will be accessed (although at a cheap price, I guess).
> For dynamic search, certainly all the ones I've done are always ANDS, how
> does this perform....
>
>> SELECT ...
>> FROM tbl
>> WHERE (key1 = @key1 AND @key1 IS NOT NULL)
>> AND (key2 = @key2 AND @key2 IS NOT NULL)
>> AND (key3 = @key3 AND @key3 IS NOT NULL)
>
> My guess (no time to check at mo) is that it will only ever use one index
> regardless of the value in @Key1, @Key2 and @Key3.
Well, looks more like a regular select on a three-column condition. :-) But
I assume that you meant
>> SELECT ...
>> FROM tbl
>> WHERE (key1 = @key1 OR @key1 IS NULL)
>> AND (key2 = @key2 OR @key2 IS NULL)
>> AND (key3 = @key3 OR @key3 IS NULL)
This will most likely not use any index at all. This is discussed further
in the article. And there is an example on how you can combine the two
methods, so that when you want the result of the latter, you can use the
former for better speed.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|