Scenarios

In the prior SQL edition, you often use combination of ROW_NUMBER and ORDER BY whenever you want to implement pagination data in SQL Server.

From SQL Server 2012, Microsoft announced a new functions to support pagination data. They are OFFSET and FETCH. In this article, I am going to introduce how to use them for paging data.

Implementation

SELECT <list of columns>

FROM <table>

ORDER BY <list of columns> ASC | DESC

OFFSET <number of rows>

FETCH FIRST|NEXT <number of rows> ROWS ONLY;

 

OFFSET: number of rows to skip, before starting return rows from the query expression. The argument of OFFSET can be an integer or expression that are greater than or equal to zero.

FETCH: Specifies the number of rows to return, after processing the OFFSET clause.

Examples

1- Return the first 10 rows from table Person.Person in AdventureWork2014

SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY BusinessEntityID ASC
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

Return the first 10 rows
Return the first 10 rows

2- Returns the next 10 rows from  table Person.Person in AdventureWork2014

SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY BusinessEntityID ASC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Return the next 10 rows from starting 11th rows
Return the next 10 rows from starting 11th rows

Using store procedure to apply pagination data

To apply pagination data from a store procedure, we often pass two parameters to it.

@PageNumber: the position of page that we want to return data.

@RowCount: number of rows that we want to returns. The value of Row Count often statistic and you often determine first. For example, you want to page 100 rows on each page.

In my example, total rows of Person.Person is 19,972 and I have determined to have 100 rows per page. So total pages is (19,971/100)+1 = 200 pages.

CREATE PROCEDURE usp_Person_GetPersonList
@PageNumber int=1,
@RowCount int =100 — Number of rows per page
AS
BEGIN
SELECT ROW_NUMBER() OVER(ORDER BY BusinessEntityID ASC) AS    RowNumber,
BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY BusinessEntityID ASC
OFFSET (@PageNumber – 1) * @RowCount ROWS
FETCH NEXT @RowCount ROWS ONLY;
END

When we pass the argument of OFFSET, we must calculate number of rows to skip using (@PageNumber – 1) * @RowCount. For example, if you want to return all rows of 100th page, the starting row is from ((100 -1) * 100 + 1) = 9,901 to 10,000.

Now, we will execute the store procedure created.

EXEC usp_Person_GetPersonList @PageNumber =100 , @RowCount = 100

Returns all rows of 100th page

 

Limitations

  • ORDER BY is mandatory to use OFFSET and FETCH clause.
  • OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
  • TOP cannot be combined with OFFSET and FETCH in the same query expression.
  • The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.

Conclusions

Beside using ROW_NUMBER() functions, OFFSET and FETCH are useful functions to support you implement the pagination data. However, they always require ORDER BY statement so in some cases you will face to performance issues on large dataset.

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s