SQL Server 2012 – Pagination with ORDER BY enhancements - SQL Programmers

With SQL Server 2012 currently available for download as a Release Candidate and an official launch coming early this year, now is a better time than ever to start learning about some of the new features it offers.

Today’s topic focuses in on the enhancements made to the ORDER BY clause, the addition of the FETCH and OFFSET parameters, and the combining of these arguments with the ORDER BY clause to paginate a result set.

Let consider the sample query below in our example.

SELECT intUserID_pk, chvFirstName, chvLastName
FROM User
ORDER BY intUserID_pk
OFFSET 5 ROWS
FETCH NEXT 5 ROW ONLY; 

As you can see in addition to a typical SELECT statement with an ORDER BY clause, I have included the OFFSET and FETCH parameters.

These parameters are used to…

• OFFSET
  • Specify a number of rows to skip before any results are returned.
• FETCH
  • Determine the number of resulting rows to return after the OFFSET pointer.

Using our example query, let’s consider the results based on the User table having 50 records with the first record having a primary key value of 1 and the last record having a primary key value of 50. As an outcome of running this query a result set of 5 records would be retrieved.

It is also worth noting that the OFFSET and FETCH parameters can only be used in conjunction with the ORDER BY clause. Any attempt to use them in the absence of the ORDER BY clause would result in an error.