My article on Dynamic Search Conditions

[Erland Sommarskog]

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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[MUKUT]

Thanks ,
It is a nice one.Go on........

[Tony Rogerson]

> 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" <es****@sommarskog.se> wrote in message
news:Xn**********************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Tony Rogerson (to**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx