How to get paged resultset from a table?
Quick and dirty example for smaller data sets.  For larger data sets I definitely recommend checking out the link Aaron Bertrand provided: http://www.sqlservercentral.com/articles/paging/69892/

declare @pagewidth int, @pagenumber int

select *
from 
    (
        select 
            *,
            row_number() over (order by PAGINGCOLUMN/S) as cnt
        from datatable
    )a
where cnt &gt; (@pagenumber - 1) * @pagewidth
    and cnt &lt;= @pagenumber * @pagewidth