SQL Server Denali now has paging functionality implemented as extension for ORDER BY clause. It is possible to select rows portion starting arbitrary row without using row_number() function.
But this functionality is not complete. Usually user need total pages/rows count. Currently user need to submit additional query which counts rows (or select all rows). It is acceptable if data is relatively static and rows count can be cached anywhere. But if row count is frequently changed then user have to submit row count query every time it select "page". It leads to additional IO.