Posted by Dave on 01/05/07 17:23

We are using SQL 2K Enterprise SP4.

Erland that was an excellent post! However it was not my problem. The
code is run from .NET application and I am testing using SQL Management
Studio.

I added this to the test script and it did not help.
SET ANSI_NULLS ON


I think there are two things going on here.
1. SQL Server is not picking the best plan
2. There is an issue with my specific data set that I can not reproduce
with any other dataset except for "some" subsets or supersets.


Thanks for the help guys!!!



Erland Sommarskog wrote:
> Dave (daveg.01@gmail.com) writes:
> > Your correlated subquery works.
> > It added a stream aggregate step to the execution plan right before the
> > merge join.
> >
> > This is really weird. Could it be an SQL Server Bug? Like I said
> > earlier, it is very data specific. I can add 3 million random records
> > to the filter table and it normally works fine. It is only this
> > specific dataset that is causing problems.
>
>
> Note that the two queries are not semantically equivalent. If a certain
> email address appears four times in the filter and four times in base, your
> original query will return 16 rows, while mine will return four.
>
> With the numbers you posted there are only four duplicates in the filter.
> Provided, that is, you are running with ANSI_NULLS ON. If this is in a
> stored procedure saved from Enterprise Manager, you are likely to be running
> with ANSI_NULLS OFF, in which case NULL = NULL, and you will get an
> enormous number of duplicates. Which are filtered away if you add the
> IS NOT NULL filter.
>
>
> --
> 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]