Thank You,

I have used first time "Outer Apply" Clause. It seems to be it is very expensive than regular Join with Drive Table. It totally make sense to make a reference from out query to inner query so we dont scan all rows.

But According to Query Analyzer it seems to be it 26 % versus 74%.

---

21%

-----

SELECT T.CO, T.TR, S.rowNo, S.ST, T.PC, T.ST, T.ET, S.AATime AS S_Actual,

DRV_Seg.AATime AS derivedtbl_1_Actual

FROM Table1 AS T INNER JOIN

Table2AS S ON T.TR = S.TR AND S.CO = T.CO LEFT OUTER JOIN

(SELECT CO, TR, rowNo, AATime

FROM NADataSource.Segment

WHERE (ST = '1')

AND (LEFT(PC, 1) 'B') AND (PC 'lhe')

AND (PC 'lhy')) AS DRV_Seg ON

S.TR = DRV_Seg.TR AND DRV_Seg.CO = S.CO AND DRV_Seg.rowNo = S.rowNo

WHERE (T.CO = N'338') AND (T.TR = '9935')

ORDER BY S.rowNo

----------

----61%

-----------

SELECT T.CO, T.TR, S.rowNo, S.ST, T.PC, T.ST, T.ET, S.AATime AS S_Actual,

DRV_Seg.AATime AS derivedtbl_1_Actual

FROM Table1 AS T INNER JOIN

Table2AS S ON T.TR = S.TR AND S.CO = T.CO

OUTER APPLY

(SELECT CO, TR, rowNo, AATime

FROM Table2S1

WHERE S.TR = S1.TR

AND S.CO = S1.CO

AND S1.rowNo = S.rowNo

AND (ST = '1')

AND (LEFT(PC, 1) 'B')

AND (PC 'lhe')

AND (PC 'lhy')

)AS DRV_Seg

WHERE (T.CO = N'338') AND (T.TR = '9935')

ORDER BY S.rowNo

------Cheapest one

---18%

SELECT T.CO, T.TR, S.rowNo, S.ST, T.PC, T.ST, T.ET, S.AATime AS S_Actual,

S1.AATime AS derivedtbl_1_Actual

FROM Table1AS T

INNER JOIN

Table2 AS S ON T.TR = S.TR AND S.CO = T.CO

LEFT OUTER JOIN

Table2 AS S1 ON T.TR = S1.TR AND S1.CO = T.CO

AND s1.ST = '1' and S.rowNo = S1.rowNo

WHERE (T.CO = N'338') AND (T.TR = '9935')

ORDER BY S.rowNo

Any body explain me what is better approach?