sql - When to use the table operator APPLY -

i'm trying understand table operator apply.

here example:

create table #y ( name char(8), hoursworked int); go insert #y values ('jim',4); insert #y values ('michael',40); insert #y values ('raj',1000); insert #y values ('jason',7); insert #y values ('tim',50); go  create table #x ( name char(8),game char(8), numbets int); go insert #x values ('jim','chess',4); insert #x values ('jim','bg',10); insert #x values ('jim','draughts',100); insert #x values ('jim','football',5); insert #x values ('michael','chess',40); insert #x values ('michael','bg',7); insert #x values ('michael','draughts',65); insert #x values ('michael','football',50); insert #x values ('raj','chess',400); insert #x values ('raj','bg',70); insert #x values ('raj','draughts',650); insert #x values ('tim','draughts',60000); go  select  y.name,          y.hoursworked,         x.game,         x.numbets    #y y         outer apply            (           select top 2 *             #x            name = y.name            order numbets         ) x order  y.name,         x.numbets desc; 

my main hurdle understanding when use apply.
i'm wondering how difficult same result above using standard sql implemented in sql-server 2005 ?
apply make query lot shorter or more readable?
if example shows no massive advantages using apply clear example there advantage using apply?

first of - apply call table-valued functions parameter values taken table query, this:

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

or shredding xml columns

select     t1.col3,     t.c.value('@value', 'int') value table1 t1     -- table1.col1 xml iike <data @value="...">...</data>     outer apply t1.col1.nodes('data') t(c)  

from expirience, apply useful when have make precalculation:

select     t1.col3,     a1.col1,  --calculated value     a2.col1   -- calculated value, first 1 used table1 t1     outer apply (select t1.col1 * 5 col1) a1     outer apply (select a1.col1 - 4 col1) a2 

another example of using apply unpivot operation:

select     t1.col1, c.name, c.value table1 t1     outer apply (         select 'col1', t1.col1 union         select 'col2', t1.col2     ) c(name, value) 

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

;with cte (     select         y.name,          y.hoursworked,         x.game,         x.numbets,         row_number() over(partition x.name order x.numbets) row_num     y         left outer join x on x.name = y.name ) select name, hoursworked, game, numbets cte row_num <= 2 order name, numbets desc 

see sql fiddle example