I ran into a situation where I wanted to use a table-valued function, which is just a function that returns a table. In my case I wanted to join that table to another one to get the results I needed.

I had not had to do that before, and I quickly found out you cannot do a standard JOIN or subquery against a table-valued function if you are also passing it a value from the table you are joining on.

This works, because the value passed to the function is a variable:

SELECT d.RecordID,
       d.StudyID,
       d.TrackingID
FROM   MyTable d
JOIN   dbo.MyFunction(@RecordID) m ON d.RecordID = m.RecordID

This does not work, because the function is being passed a column from the table on the left:

SELECT d.RecordID,
       d.StudyID,
       d.TrackingID
FROM   MyTable d
JOIN   dbo.MyFunction(d.RecordID) m ON d.RecordID = m.RecordID

I could have dropped the function and written the whole thing as a subquery, but the point was not just to get it working. I wanted to keep that helper function available, because the logic inside it is used in several places and I did not want three copies of it drifting apart.

CROSS APPLY makes it trivial:

SELECT      d.RecordID,
            d.StudyID,
            d.TrackingID
FROM        MyTable d
CROSS APPLY dbo.MyFunction(d.RecordID) m

APPLY works like a JOIN without the ON clause, and it comes in two forms. CROSS APPLY returns a row from the left side only when the function returns rows for it. OUTER APPLY returns every row on the left whether the function gave it anything or not, with nulls in the function's columns where it did not.

That returned the data I needed and let me keep the logic in one place. As with any query, test the performance against your own data before committing to it. I tried a couple of other approaches and none of them ran better, and none of them let me reuse the logic as easily, which is why CROSS APPLY won.