Paging implementing on Store Procedure in Microsoft Sql Server 2012

Microsoft Sql Server 2012 introduces an interesting functionality using OFFSET  and FETCH with existing ORDER By Clause. Using OFFSET and FETCH achieving  paging functionality.

Developer can write SQL query for fetch only a set of rows from the complete result set windows and improving performance when fetching and display number of record from table.

Now time for example how to achieving paging using OFFSET and FETCH feature in Sql Server 2012.

SELECT

    Name,

    City,

    State

FROM    Customer

ORDER BY City

   OFFSET 0 ROWS

        FETCH NEXT 5 ROWS ONLY

Here if you want to write dynamic TSQL query for paging.

Declare @pageNo int =3

Declare @pageSize int =10

--Calculating OFFSET

Declare @offSet int = (@pageNo * @pageSize)  - @pageSize

Now writing above paging query with dynamic parameter.

SELECT

    Name,

    City,

    State

FROM    Customer

ORDER BY City

   OFFSET @offSet ROWS

        FETCH NEXT @pageSize ROWS ONLY

Hope you have enjoy sql server 2012 paging feature!!