As I posted here, I’m very interested in using CROSS APPLY in a new way to keep down clutter in sql statements with complex column expressions. I recently found a limitation. I think I’ve figured out why, and since understanding reduces frustration I’m posting it here.The following query works fine and gives you row numbers in the order of the object ids in sys.all_objects:

USE model
GO
SELECT TOP 10
ROW_NUMBER() OVER (ORDER BY object_id) As RowNum
,*
FROM sys.all_objects

This one doesn’t, it gives you a ROW_NUMBER() of “1” in every case.

SELECT TOP 10
RowNum
,*
FROM sys.all_objects
CROSS APPLY(SELECT ROW_NUMBER() OVER (ORDER BY object_id) As RowNum) CA

If you look at MSDN you find that CROSS APPLY

…specifies that the right_table_source of the APPLY operator is evaluated against every row of the left_table_source. This functionality is useful when the right_table_source contains a table-valued function that takes column values from the left_table_source as one of its arguments.

Incidentally, this is different to Oracle, where a pipelined function returning a table can be used in a conventional JOIN expression. I think it’s the evaluation against each row that causes the behaviour; the CROSS APPLY doesn’t see the whole set, only the row that has been passed to it, which will therefore rank as number one.

So the way that these functions behave in a CROSS APPLY seems consistent and I don’t think it’s even much of a loss, considering how useful CROSS APPLY is in other ways.