My Article On Dynamic Search Conditions

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, Join Bytes!

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx