|
|
Posted by Tony Rogerson on 10/02/95 11:49
> 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?
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.
Tony
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns97D8EF76BFA03Yazorman@127.0.0.1...
> I've uploaded a new version of my article on Dynamic Search Conditions
> on http://www.sommarskog.se/dyn-search.html. I've revised the article to
> cover SQL 2005, and made a general overhaul of the content. There was a
> *very* embarrassing error that I've corrected.
>
> I've also added a new interesting method for static SQL. I've found that
> if
> you say:
>
> 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)
>
> This will use indexes if all columns are indexed, and furthermore SQL
> Server will decide at run-time which index(es) to access. The article
> includes a trick where you can combine this with the normal conditions for
> dynamic searches for very good performance under some circumstances.
>
> I also cover the new OPTION (RECOMPILE) to force statement recompile.
> I was hoping that it could lead to just as good query plans as dynamic
> SQL, but it's far cry from that.
>
> --
> 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]
|