sql server - SQL - Using CTE and effectively select rows at a specific row_number() -

in scenario select top necessary within cte (micro optimization, know...).

declare @pagesize int, @currentpage int, @top int set @pagesize = 10 set @currentpage = 150 set @top = @pagesize * @currentpage + @pagesize  t (     select top(***@top***) id, name     row_number() on (order id) _row,     dbo.user ) select top(@pagesize) * t t._row > (@top-@pagesize) order t.id 

the above returns 10 (@pagesize) rows start number (@top-@pagesize) in specific order row number column. cte statement know "select top" outside of cte , where-clause, outside cte, come, hence cte never returns more rows in specific order needed?

basically talking row_number function, not count row number rows not returned (if have millions of rows...), , if select top 100 in cte, row_number still calculated million rows within table selected?

i have tried , without "select top(@top)" in cte-statement, inside loop 10.000 runs, without seeing difference in time usage. though, have 38.000 rows in table @ moment.

edit: result:

    t (     **do top() order in cte**     select top(@top) id, name      row_number() on (order id) _row,     dbo.user     order id ) select top(@pagesize) *  **selecting top n cte, row-number ... due cte in order already** t t._row > (@top-@pagesize) 

this more efficient if ordered them "backwards", selecting "bottom @pagesize" of cte, leave out where-clause... require test if faster...

the use of top without order by discouraged. there no guarantee rows want, should not include top. or, should include order id, if ordering want.

the user of top doesn't affect row_number() calculation, because calculation going done before top applied. can imagine having window function there, such sum() on () understand top cannot applied before row_number() , finding circumstances safe hard work.