SQL Server 2012 (Denali) New Features: Limit number of rows using OFFSET and FETCH clause

Limiting the number of rows returned

The following example specifies an integer constant as the value for the OFFSET and FETCH clauses. The first query returns all rows sorted by the column ID. Compare the results returned by this query with the results of the two queries that follow it. The next query uses the clause OFFSET 5 ROWS to skip the first 5 rows and return all remaining rows. The final query uses the clause OFFSET 0 ROWS to start with the first row and then uses FETCH NEXT 10 ROWS ONLY to limit the rows returned to 10 rows from the sorted result set.

-- Return all rows sorted by the column DepartmentID.
SELECT ID, Name, GroupName
FROM Department
ORDER BY ID;

-- Skip the first 5 rows from the sorted result set and return all remaining rows.
SELECT ID, Name, GroupName
FROM Department
ORDER BY ID OFFSET 5 ROWS;

-- Skip 0 rows and return only the first 10 rows from the sorted result set.
SELECT ID, Name, GroupName
FROM Department
ORDER BY ID
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;