SQL Server 2011 - Offset

One very simple, but very useful new feature of Denali, is the OFFSET clause, which can be used in conjunction with ORDER BY, to return just a specified number of rows from a table.

For example, if I run a SELECT * against the Sales.SalesOrderDetail table in the AdventureWorks2008 database, I return 121317 rows, starting with SalesOrderDetailID 1.

If however, I run the following query...

SELECT
     *
FROM
     Sales.SalesOrderDetail
ORDER BY
     SalesOrderDetailID
          OFFSET 10 ROWS

...I skip the first 10 rows, based on SalesOrderDetailID, and return the following results...

...As you can see, I have only returned 121307 rows, and have missed out the first 10 rows, based on SalesOrderDetailID.

I can also limit, how many rows will be returned after the offset. For example, if I run the following query...

SELECT
    *
FROM
    Sales.SalesOrderDetail
ORDER BY
    SalesOrderDetailID
         OFFSET 100 ROWS
         FETCH NEXT 10 ROWS ONLY

..I return only 10 rows, with SalesOrderDetailIDs from 101 through to 110, as you can see below...

...One big bonus, is that the OFFSET statement can be fully parametrised, so for example, I could run the following query to return identical results...

DECLARE @Offset INT
DECLARE @Limit INT

SET @Offset = 100
SET @Limit = 10

SELECT
    *
FROM
    Sales.SalesOrderDetail
ORDER BY
    SalesOrderDetailID
         OFFSET @Offset ROWS
         FETCH NEXT @Limit ROWS ONLY

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom