How Can We Do Pagination In Fetching The Value With 100 Records Each In Sql

How can we do fetching first 100 records next 100 records then next and so on in SQl Server from a table

Solution 1:

Use CTE and OFFSET:

@RecordIndex=StartRowNo@PageSize=NoofRowstofetch

;WITH CTE_Results
AS (
SELECTROW_NUMBER() OVER (ORDERBY CreatedDate DESC) AS ROWNUM,
    Count(*) over () AS TotalCount,
    *FROM TableName  
)      
Select*from CTE_Results 
ORDERBY ROWNUM
OFFSET (@RecordIndex) ROWSFETCH NEXT @PageSizeROWSONLY;

Solution 2:

Here is the procedure that will give you pagination based on the page number and the record count. By default the procedure will return first 100 records from your table.

1. Create the below procedure in your database. Make sure you have mention the name of your table and ordering column.

   CREATEPROCEDURE [dbo].[Fetchdata]
       @pagenoINT=1,
       @pagesizeINT=100ASBEGINSET NOCOUNT ON;    
   
   DECLARE@sqlVARCHAR(MAX)='    
               SELECT *
               FROM YOURTABLE
               ORDER BY [YOURCOLUMN]
               OFFSET ('+CONVERT(VARCHAR(50),@pageno)+' - 1) * '+CONVERT(VARCHAR(50),@pagesize)
                   +' ROWS FETCH NEXT '+CONVERT(VARCHAR(50),@pagesize)+' ROWS ONLY;'EXEC (@sql)
   
   
    END

2. use the below script to execute the procedure,with page number and page size as input.

   EXEC [FetchData] @pageno=2 ,@pagesize=100

Solution 3:

Using row_number...

;with cte
as
(
select*,row_number() over (orderby someuniquekey) as paging
)
select*fromwhere paging between1and100

you could also use Offset and Fetch like below,,

select*fromtableorderby someuniquekey
offset0rowsfetch next 100rowsonly

Parameterized version of above ..

-- parameterizedDECLARE@pagenumASINT=2,
  @pagesizeASINT=100;

SELECT*FROMtableORDERBY someuniquekey
OFFSET (@pagenum-1) *@pagesizeROWSFETCH NEXT @pagesizeROWSONLY;

Further reading..http://sqlmag.com/blog/sql-server-2012-t-sql-glance-offsetfetch

Solution 4:

You can refer this page for your reference, it is same question related to paginatin

How to do pagination in SQL Server 2008