Posted by Beowulf on 05/05/06 18:38

Erland Sommarskog wrote:
> Beowulf (beowulf_is_not_here@hotmail.com) writes:
>> I have the following function(s) that each joins an active record with
>> it's most recent record in an audit trail table, to show differences.
>> I've perused the execution plans for 2 versions of this function in QA
>> and it seems to me that the two versions execute identically - that is,
>> the addressing the sub-query of the WHERE clause with either IN or =
>> results in the same execution plan.
>>
>> Does this seem plausible or did I misinterpret something in QA?
>
> It seems very plausible.
>

So, the SQL Server query optimizer is smart enough to figure out that
the sub-query in the WHERE clause will return only a single value and is
able to figure out that WHERE x IN (a) is the same as WHERE x = a.
That's pretty cool.

> I nevertheless prefer the version with =. But that is merely because
> it's easier to read and understand.

Thanks for the input. I certainly agree.

• Next in forum: Re: how to find conditions across rows (attendance)
• Prev in forum: Re: DTSRUN privileges?
• Thread view: Re: Selecting MAX (Date) - Performance Question

[Reply to this message]