Posted by Beowulf on 05/03/06 22:10

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

)

• Next in forum: How to do an equivelent First() operation
• Prev in forum: Re: SQL Counting number of non-distinct rows?
• Thread view: Selecting MAX (Date) - Performance Question

[Reply to this message]