0

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

Thomas
  • 1,445
  • 14
  • 30
  • 2
    Possible duplicate of [How to do pagination in SQL Server 2008](http://stackoverflow.com/questions/2244322/how-to-do-pagination-in-sql-server-2008) – Nhan Aug 23 '16 at 02:49

4 Answers4

1

Use CTE and OFFSET:

@RecordIndex=Start Row No
@PageSize=No of Rows to fetch

;WITH CTE_Results
AS (
SELECT 
    ROW_NUMBER() OVER (ORDER BY CreatedDate DESC) AS ROWNUM,
    Count(*) over () AS TotalCount,
    *
    FROM TableName  
)      
Select * from CTE_Results 
ORDER BY ROWNUM
OFFSET (@RecordIndex) ROWS
FETCH NEXT @PageSize ROWS ONLY;
Sandip - Frontend Developer
  • 14,397
  • 4
  • 35
  • 61
1

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.

    CREATE PROCEDURE [dbo].[Fetchdata]
    @pageno INT=1,
    @pagesize INT=100
AS
BEGIN

SET NOCOUNT ON;    

DECLARE @sql VARCHAR(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
Unnikrishnan R
  • 4,965
  • 1
  • 12
  • 21
  • here ,OFFSET ('+CONVERT(VARCHAR(50),@pageno)+' - 1) * '+CONVERT(VARCHAR(50),@pagesize) +' ROWS FETCH NEXT '+CONVERT(VARCHAR(50),@pagesize)+' ROWS ONLY;' it showinf the error – Thomas Aug 22 '16 at 13:39
  • could you please explain what is the exact error that you are getting?? In this procedure you have to pass the page number and the page size,ie. the number of rows that you want from the table. – Unnikrishnan R Aug 22 '16 at 13:45
  • means it shoing error near off set , now it is correct beacuse i put ',' before offset it was the pblm – Thomas Aug 22 '16 at 13:49
  • by using the VARCHAR(MAX)= it will take more time to fetch or not? – Thomas Aug 22 '16 at 13:50
  • nope ,its just storing the query string.. fetching will take place in the next step.. EXEC(@sql) – Unnikrishnan R Aug 22 '16 at 13:53
0

Using row_number...

;with cte
as
(
select *,row_number() over (order by someuniquekey) as paging
)
select * from where paging between 1 and 100

you could also use Offset and Fetch like below,,

select 
* from table
order by someuniquekey
offset 0 rows fetch next 100 rows only

Parameterized version of above ..

-- parameterized
DECLARE 
  @pagenum  AS INT = 2,
  @pagesize AS INT = 100;

SELECT *
FROM table
ORDER BY someuniquekey
OFFSET (@pagenum - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY;

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

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
0

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

How to do pagination in SQL Server 2008

Community
  • 1
  • 1