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) *

        FROM Citymaster

        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 )

        , *

    FROM Citymaster

)

SELECT *

FROM results

WHERE rowNo between (@pageNumber-1)*@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 ((@pageNumber-1)*@pageSize) AND (@pageNumber*@pageSize)

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