Why does window functions not work in CROSS APPLY?

Question

There is a simple code. I always thought that both outside ROW_NUMBER and the one in CROSS APPLY clause are supposed to generate the same output (in my example I excepct rn = crn). Could you please explain why isn't it like that?

CREATE TABLE #tmp ( id INT, name VARCHAR(200) );


INSERT  INTO #tmp
VALUES  ( 1, 'a' ),
        ( 2, 'a' ),
        ( 3, 'a' ),
        ( 4, 'b' ),
        ( 5, 'b' ),
        ( 6, 'c' ),
        ( 7, 'a' );


SELECT  name,
        ROW_NUMBER() OVER ( PARTITION BY name ORDER BY id ) AS rn,
        a.crn
FROM    #tmp
        CROSS APPLY (
                        SELECT  ROW_NUMBER() OVER ( PARTITION BY name ORDER BY id ) AS crn
                    ) a;

OUTPUT:

name    rn  crn
a   1   1
a   2   1
a   3   1
a   4   1
b   1   1
b   2   1
c   1   1

Answer 1

The query in the CROSS APPLY is applied to each row in #tmp. The query selects for that one row it is applied to, the row number for that one row which is of course one.

Maybe this article on Microsoft's Technet would give you more insight into how CROSS APPLY works. An excerpt that highlights what I wrote in previous paragraph:

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

Answer 2

Note that APPLY is using the fields from you main query as the parameters.

SELECT  ROW_NUMBER() OVER ( PARTITION BY name ORDER BY id ) AS crn

The above query does not have a FROM clause. So it's treating the name and id as literals. To illustrate, for the first row of #tmp, the resulting query of the CROSS APPLY is:

SELECT  ROW_NUMBER() OVER ( PARTITION BY (SELECT 'a') ORDER BY (SELECT 1)) AS crn

which returns:

crn
--------------------
1

This is the result of your CROSS APPLY for every rows.

To achieve the desired result:

SELECT  
    t.name,
    ROW_NUMBER() OVER ( PARTITION BY t.name ORDER BY t.id ) AS rn,
    a.crn
FROM #tmp t
CROSS APPLY(
    SELECT id, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id ) AS crn
    FROM #tmp
) a
WHERE t.id = a.id