Custom Paging using SQL Server 2012

Implementing Custom Paging in .net with SQL Server 2012

In this new version/edition, we have a new clause "OFFSET FETCH Next" that extends the existing ORDER BY clause. This is something like a ready-made option for paging. Before this there was no direct function/clause to implement custom paging. OFFSET specifies the number of rows to skip and FETCH specifies the number of rows to return (after skipping rows provided by the OFFSET clause). 

The following syntax is taken from Microsoft's MSDN for a better view of it:

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }

FETCH { FIRST|NEXT } <rowcount expression> { ROW|ROWS } ONLY

Implementing custom paging in Microsoft SQL Server 2012 is very easy compared to earlier versions. It reads only the required number of rows and thus saves the IO as well CPU time that occurs from reading excess rows. It works like the TOP clause with Order By. So it gives better performance than the temp table mechanism.

A simple example is:

CREATE PROCEDURE dbo.uspGetPageRecords

(    

    @OffSetRowNo INT,    
    @FetchRowNo INT

) AS 
SELECT AutoID, Name, City, RegistrationDate 

FROM tblUserMaster

ORDER BY RegistrationDate
OFFSET (@PageNo-1)*@RecordsPerPage ROWS

FETCH NEXT @RecordsPerPage ROWS ONLY

GO


The following are the limitations of using Offset Fetch:

1. Fetch Next can't be used standalone, it requires Offset 
2. Offset can't be used standalone, it requires order by 
3. Top can't be combined with offset fetch next in the same query expression

Read More...


:)

Share on Google Plus

About Unknown

A social executive and programmer by passion. He contributes in technical forums along with Career Advice on many websites. Read More ...

---

Join him on : Google+ | Facebook | Twitter