Pagination Query in Sql server

By
Advertisement
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.


3 comments:

  1. I run the script of "Pagination in sql server 2000", it comes an error message :

    Server: Msg 170, Level 15, State 1, Line 6
    Line 6: Incorrect syntax near '('.

    ReplyDelete
    Replies
    1. Can you post ur query here.. along with table details...

      Delete
    2. declare @pageNumber int, @pageSize int
      set @pageNumber = 2
      set @pageSize = 3

      SELECT TOP (@pageNumber) *
      FROM Top_5
      ORDER BY TM ASC

      ==================
      I use SQL Server 2000

      Table Top_5 :
      -------------------
      Dealer_ID (nvarchar 5)
      ...
      ...
      TM (int 4) ----> TM = This Month Sales

      Delete

Online Casino