CODEWITHSUNDEEP
phpsql-serversql-server-2005
Raghav Motwani
Raghav Motwani

Reputation: 169

Pagination of records with MSSQL 2005

I am trying to paginate the records fetched into pages of 10 (using PHP AJAX on MSSQL Server 2005). (I am passing SQL as PHP strings to MSSQL server) I wish to display button for each page (for example if there are 45 records then I wish to display 5 buttons with each button fetching 10 records). I have came across this code-

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum <= 10
ORDER BY RowNum

But this is highly ineffective because firstly whole database is fetched then from it 10 rows is fetched every time. Could anyone suggest some technique (that works with MSSQL server 2005) wihich fetches only 10 rows at a time and not the whole 100 records at a time. The above method also includes an extra column (it requires a primary key). I wish to get rid of that column too!

Also, since I have to display the page numbers (pagination tabs), I wish to have a count of total number of records beforehand.

Thanks!

Upvotes: 1

Views: 189

Answers (1)

Petter Pettersson
Petter Pettersson

Reputation: 377

Learn to use LINQ. Very efficient.

Edit
Well then.

SELECT * 
FROM etc
WHERE Row_Number() BETWEEN '".$startLimit."' AND '".$endLimit."'

Might work for you. Remember to escape the variables so no dangerous code can bass by.

Upvotes: 1

Related Questions