When To Use The Table Operator Apply

I'm trying to understand the table operator APPLY. Here is the example: CREATE TABLE #y ( Name char(8), hoursWorked int); GO INSERT INTO #y VALUES ('jim',4); INSERT INTO #y VALUES

Solution 1:

First of all - with apply you could call table-valued functions where parameter values are taken from table you query, something like this:

select
    t1.col3, -- column from table
    f1.col1  -- column fromfunctionfrom table1 as t1
    left outer join table2 as t2 on t2.col1 = t1.col1
    outer apply dbo.function1(t1.col1, t2.col2) as f1

or shredding xml columns

select
    t1.col3,
    t.c.value('@value', 'int') asvaluefrom table1 as t1
    -- table1.col1 is xml iike <Data @Value="...">...</Data>
    outer apply t1.col1.nodes('Data') ast(c) 

From my expirience, apply is very useful when you have to make some precalculation:

select
    t1.col3,
    a1.col1,  --calculated value
    a2.col1   -- another calculated value, first one was used
from table1 as t1
    outer apply (select t1.col1 * 5as col1) as a1
    outer apply (select a1.col1 - 4as col1) as a2

another example of using apply is unpivot operation:

select
    t1.col1, c.name, c.value
from table1 as t1
    outer apply (
        select'col1', t1.col1 unionallselect'col2', t1.col2
    ) as c(name, value)

finally, here's your query implemented in terms of SQL 2005 without using apply:

;with cte as (
    select
        y.Name, 
        y.hoursWorked,
        x.game,
        x.NumBets,
        row_number() over(partitionby x.Name orderby x.NumBets) as row_num
    from y
        leftouterjoin x on x.Name = y.Name
)
select Name, hoursWorked, game, NumBets
from cte
where row_num <=2orderby Name, NumBets desc

see sql fiddle example