Selecting MAX (Date) - Performance Question

[Beowulf]

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?

---

ALTER FUNCTION Function9()
RETURNS TABLE
AS RETURN
(
SELECT ID, Numb, Name, Q.Diff_ID, Q.Diff_Numb, Q.Diff_Name,
FROM tblQuestion INNER JOIN
(
SELECT ID AS Diff_ID, Numb AS Diff_Numb, Name as Diff_Name

FROM tblQuestion_Audit
WHERE AuditDate IN (SELECT MAX(AuditDate) FROM tblQuestion_Audit)
) AS Q
ON dbo.tblItem.ID = Q.Diff_ID

)

ALTER FUNCTION Function10()
RETURNS TABLE
AS RETURN
(
SELECT ID, Numb, Name, Q.Diff_ID, Q.Diff_Numb, Q.Diff_Name,
FROM tblQuestion INNER JOIN
(
SELECT ID AS Diff_ID, Numb AS Diff_Numb, Name as Diff_Name

FROM tblQuestion_Audit
WHERE AuditDate = (SELECT MAX(AuditDate) FROM tblQuestion_Audit)
) AS Q
ON dbo.tblItem.ID = Q.Diff_ID

)

[Erland Sommarskog]

Beowulf (be*****************@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.

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

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Beowulf]

Erland Sommarskog wrote:

Beowulf (be*****************@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.

[Erland Sommarskog]

Beowulf (be*****************@hotmail.com) writes:

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.

That's a fairly trivial exercise for the optimizer, I'd say. :-)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx