Custom Search
Logiclabz

Pagination in Sql Server 2005

  

Generating Pagination in Sql Server 2005 using ROW_NUMBER() function

Method 1: 

DECLARE @rowsPerPage int,
@pageNum int,
@startRow int,
@endRow int

SET @rowsPerPage = 10
SET @pageNum = 3

SET @startRow = ((@pageNum- 1) * @rowsPerPage)+1
SET @endRow = @startRow + @rowsPerPage -1

SELECT * FROM (
SELECT row_number() OVER (ORDER BY id) as resultNum,
id FROM myTable
) as numberResults
WHERE resultNum BETWEEN @startRow AND @endRow

Method 2 (Recommended Method): 

SET @rowsPerPage = 10
SET @pageNum = 3

With SQLPaging
As
(
Select Top(@rowsPerPage * @pageNum) ROW_NUMBER() OVER (ORDER BY id) as resultNum, id
FROM myTable
)
select * from SQLPaging where resultNum > ((@pageNum - 1) * @rowsPerPage)



  


| | More

Leave a reply


Comments

  • César says:
    May 05, 09

    I didn't know the method 2. Very useful. Thanks from Chile

  • Randhir says:
    Jul 05, 09

    I saw both of your ways of paging in sql server 2005. Both code are much more efficient than we used to do earlier. I just wanted to make sure, why the second method that you have recommended in this article is efficient. My reason is: The second method is efficient because it extracts records beginning from the first record to only the records that are meant to be returned. while the first method of paging is less efficient because it extracts all the available records that matches the condition in select statement. I just wanted to know. Am i right. Please let me know. Thanks Randhir

  • logiclabz says:
    Jul 05, 09

    Thanks Randhir, your reason is perfectly right.

  • Vito Botta says:
    Nov 02, 09

    Any particular reason why you would recommend the second method rather than the first one?

  • Arun Prasad Vidhyaarthi says:
    Sep 17, 10

    Thumbs up to Method 2. Was longing for exactly for code of this efficiency. Good thinking. Keep up the excellent work...


Related Posts


Do you like this post?
Subscribe to RSS Follow us on Twitter

Categories

Tools

Recent Posts