|
|
Posted by Erland Sommarskog on 10/02/43 11:49
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]
|