MS SQL 2005: Where clause and optimizer

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.

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

Boaboa sema (boasema@.gmail.com) writes:

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

boa sema (boasema@.gmail.com) writes:
>

Quote:

Originally Posted by

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

That's what I was thinking of. It was a very, very long time ago,
impressing that you still remember the details as well as you do.

Now that you've clarified it so well, this is not an issue anymore. I
remembered incorrectly.

Thanks.
Boa

[snip]

MS SQL 2000 - SQL Select with XPATH Where Clause

In MS SQL Server 2000, is there an easy way to use an XPATH statement in a
WHERE clause? I have a table with a TEXT column that contains XML. I would
like to query this table and select any rows where the XML data field meets
my XPATH criteria.
I'm attempting to avoid using cursors and sp_xml_preparedocument on each row
of data in the table, but with SQL Server 2000, that may be my only option.
Any help would be appreciated!Bryan (Bryan@.discussions.microsoft.com) writes:
> In MS SQL Server 2000, is there an easy way to use an XPATH statement in
> a WHERE clause?
Yeah, upgrade the instance to SQL 2005. :-)

> I have a table with a TEXT column that contains XML. I
> would like to query this table and select any rows where the XML data
> field meets my XPATH criteria.
> I'm attempting to avoid using cursors and sp_xml_preparedocument on each
> row of data in the table, but with SQL Server 2000, that may be my only
> option.
OPENXML is on the only XML support that SQL 2000 offers. I agree that a
cursor and preparedocument for each is not appealing. The other alternative
would be to bring the data to the client, but is probably even less
appealing.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
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