Pagination Query in Sql server

I was asked a question or a series of questions, “How would you do paging in a SQL? How would you do this with a lot of records, say, 10,000 or more?”

I thought about answers. To be more precise, I thought of more questions and this got me thinking, “This must be a common problem, every developer must have done or solved this. What about paging sizes and working with very large data sets? What about getting results from multiple tables?”

So, I decided to look into these questions and found the queries that works in different versions of sql server

Pagination in sql server 2000

declare@pageNumber int,@pageSize int
set@pageNumber=2
set @pageSize=10
SELECT * FROM
(
    SELECT TOP (@pageSize) * FROM
    (
        SELECT TOP (@pageNumber * @pageSize) *
        FROMCitymaster
        ORDER BY CityID ASC
    ) AS t1
    ORDER BY CityID DESC
) AS t2
ORDER BY CityID ASC


Pagination in sql server 2005

Option- 1

declare@pageNumber int,@pageSize int
set@pageNumber=2
set @pageSize=10
;WITH results AS (
    SELECT
        rowNo =ROW_NUMBER()OVER( ORDER BY Cityid ASC )
        , *
    FROMCitymaster
)
SELECT *
FROM results
WHERE rowNo between (@pageNumber1)*@pageSize+1 and @pageNumber*@pageSize


Option- 2

declare@pageNumber int,@pageSize int
set@pageNumber=2
set @pageSize=10
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY Cityid) RowNr, * FROM Citymaster) t
WHERE RowNr BETWEEN ((@pageNumber1)*@pageSize) AND (@pageNumber*@pageSize)

If you face any issue in implementing this code please comment here. We will help you.

Comments

  1. By Anonymous

    Reply

  2. Reply

  3. By Anonymous

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *