Paging Data In SQL Server With OFFSET and FETCH

Paging Data In SQL Server With OFFSET and FETCH

In this tutorial we will learn how to do Paging Data In SQL Server With OFFSET and FETCH. This functionality is very useful if application development. Instead of having the entire result returned, it is often preferable to have a short list broken down by a page and some number of rows. Prior to SQL Server 2012 developers use many techniques to do the same, SQL Server 2012 introduces a keyword coupling that provides an elegant and efficient paging solution. Using OFFSET and FETCH, you can write a single query that returns data one page at a time to a client application or end user.

In previous post we had discussed Page Compression In SQL Server and Row Compression in SQL Server.

OFFSET Denotes how many rows to skip before the query starts returning rows.
FETCH Specifies how many rows to return after OFFSET has been processed.

OFFSET refers the page number and FETCH with the number of rows that will be displayed per page.
Both OFFSET and FETCH have few additional arguments that must be included in the syntax. The following is sample syntax for writing a paging query:

Query Example:

USE AdventureWorks2012
GO
SELECT
ProductID,
ProductNumber,
Name AS ProductName,
ListPrice
FROM Production.Product
ORDER BY ProductID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY