Posted by Erland Sommarskog on 02/01/07 11:40

boa sema (boasema@gmail.com) writes:
> Way back when, and at least in version 7 IIRC, the query optimizer gave
> up when the where clause in a statement contained more than 4 search
> conditions.

This is a complete misunderstanding. What you might be thinking of is
that in SQL 6.5 and earlier version, the optimizer would only consider
four tables at a time, so if you had a query like:

SELECT ...
FROM a, b, c, d, e
WHERE ....

the optimizer would first look at (a, b, c, d) as a group, and then
(b, c, d, e) as a group. This mean that if the best plan was to start
with e and then take a next, the optimizer would most likely not find
plan.

> Does anyone know if such a limitation still exist in MS SQL 2005? The
> BOL seems to be silent on the issue.

The behaviour I describe above was abolished in SQL 7. The optimizer does
still not consider all possible plans - because for 12-table query it could
spend all day optimising. Instead it considers it task finished when the
estimated cost is good enough.

--
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

[Back to original message]