Paging in SQL Server 2012 using Offset and Fetch

Paging in SQL Server 2012 using Offset and Fetch

The OFFSET and FETCH clause in SQL Server 2012 provides an option to fetch only a page or limited set of the results from the complete result set.

Offset provides a starting row from which result set is display.

Fetch is use to display number of row which are to be retrieved from the result set.

Using these two we create paging in SQL Server. Lets take a simple example in which we directly query with SQL Server using Offset and Fetch.

SELECT * FROM [dbo].[YourTableName] ORDER BY ID 

offset ((@IndexValue - 1) * PageSize) ROWS

FETCH NEXT @PageSize ROW ONLY

In this we have to put value of @IndexValue and @PageSize.

Now using this, lets make Store Procedure for Paging  

CREATE PROC [dbo].[spGetPagedDispatch]

(

@Index INT=1,

@PageSize INT=5,

@TotalRecordCount INT OUTPUT

)

AS 

BEGIN

SELECT * FROM [dbo].[YourTableName] ORDER BY ID

offset ((@Index - 1) * @PageSize) ROWS

FETCH NEXT @PageSize ROW ONLY

SET @TotalRecordCount = (SELECT COUNT(*) FROM [dbo].[YourTableName]  )

END

Hear we pass @Index and @PageSize as a parameter. If user doesn't supply these value it will take default values of 1 and 5 respectively.

@TotalRecordCount is the output parameter which gives the total record count within the table.

It will be used to display total number of pages and for other purposes.

Limitation : 

• OFFSET and FETCH are only support in SQL Server 2012.

• ORDER BY is mandatory to use OFFSET and FETCH.

• OFFSET clause is mandatory with FETCH.