Posted by Erland Sommarskog on 01/05/07 08:24

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]