Pagination in SQL Server

Pagination is one of the intersting problem faced by all kind of applications. Pagination in earlier version of SQL Server is achieved by Common Table Expression(CTE). SQL Server 2012 integrated pagination syntax in most elegant and intutive way.

Let's assume the following sample table

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[test](
 [val] [int] NULL
) ON [PRIMARY]

GO

Fill the table with integer 1 to 100. Let's assume that we want to find the 5th page with page size of 5 records. That means we want record 20 to 25

Here is the simple syntax to achieve the pagination.

SELECT val FROM test ORDER BY val OFFSET 20 ROWS FETCH NEXT 5 ROWS ONLY

OFFSET specifies starting point and FETCH NEXT X ROWS specifies the PageSize. This feature will work only if you use orderby. MySQL has this feature long time back.This is one of the most expected feature from SQL Server.Finally SQL Server 2012 came up with this feature.


Happy Coding.