Re: Need help with hint syntax in SQL Server 2000

On Dec 15, 5:16 pm, Hugo Kornelis
<h...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
On Mon, 15 Dec 2008 12:16:40 -0800 (PST), Mark D Powell wrote:
Anyone able to explain why SQL Server was
unable to recognize that it shoul push the where clause condition down
to the table access?

Hi Mark,

Based on your very generic description, the only thing I can say is that
I would first check if the UNION ALL has something to do with it.

For more specific comments, though, we need more information. For
starters, we need the design of all tables and views involved (posted as
CREATE TABLE and CREATE VIEW statements, including all properties,
constraints, and indexes). Some sample data might help as well, though
in this case I would definitely want to see an execution plan (captured
with SET SHOWPLAN_ALL ON) and information about numbers of matching rows
in the various tables.

You can of course replace table and column names to protect sensitive
data, and you can also (in fact, I even encourage you to) simplify the
problem as far as possible.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

Thank you both for the replies. With the holidays I do not know if I
will be able to devote some time to trying to figure out what stopped
SQL Server from pushing the filter condition down but from the replies
I take it that normally it can do so. We do not have very many
complex views in use in our sytem so this is the first time I have
encountered a proble like this. Tryng to test a version of the view
with the union all removed should not be time consuming and I do not
remember if I looked for a difference in data type definitions. I can
try to double check that.

-- Mark D Powell --
.

Relevant Pages

  • Re: SQL Humor
    ... Hugo Kornelis wrote: ... SQL Server will often produce the same execution for both versions. ... the big questions for the RM, *for me* seem to include view updatability which has to do with the operators of the algebra as well as whether a relational engine can implement customary features such as concurrency control and presentation coherence without being written in a language that eschews the relational operators. ...
    (comp.databases.theory)
  • Re: Help with missing data in query
    ... I did go to that site But I couldn't figure out how to upload anything. ... GROUP BY m.MonthName WITH ROLLUP ... Hugo Kornelis, SQL Server MVP ...
    (microsoft.public.sqlserver.mseq)
  • Re: Multiplying numeric(19,4) Values
    ... in the context of my original query: ... I don't think I suggested that in the context of this query. ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis ...
    (microsoft.public.sqlserver.programming)
  • Re: How to Gnerate a Random ID Number
    ... formula with a seed as input to get at a pseudo-random value?) ... that definitely rules out newid() as a "good" pseudo random number ... this on SQL Server 2005, and the seed does not seem to be reset (or at ... Hugo Kornelis, SQL Server MVP ...
    (comp.databases.ms-sqlserver)
  • Re: Need help with hint syntax in SQL Server 2000
    ... unable to recognize that it shoul push the where clause condition down ... Some sample data might help as well, ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (comp.databases.ms-sqlserver)