Physical paging can be performed by using the SKIP and LIMIT sub-clauses in the ORDER BY clause. To perform physical paging deterministically, you should use SKIP and LIMIT. If you only want to restrict the number of rows in the result in a non-determinsitic way, you should use TOP. TOP and SKIP/LIMIT are mutually exclusive.
The SELECT clause can have an optional TOP sub-clause following the optional ALL/DISTINCT modifier. The TOP sub-clause specifies that only the first set of rows will be returned from the query result. For more information, see TOP.
SKIP And LIMIT Overview
SKIP and LIMIT are part of the ORDER BY clause. If a SKIP expression sub-clause is present in a ORDER BY clause, the results will be sorted according to the sort specification and the result set will include row(s) starting from the next row immediately after the SKIP expression. For example, SKIP 5 will skip the first five rows and return from the sixth row forward. If a LIMIT expression sub-clause is present in an ORDER BY clause, the query will be sorted according to the sort specification and the resulting number of rows will be restricted by the LIMIT expression. For instance, LIMIT 5 will restrict the result set to five instances or rows. SKIP and LIMIT do not have to be used together; you can use just SKIP or just LIMIT with ORDER BY clause. For more information, see the following topics: