Pagination in Sql Server 2005

Method 1:

1. DECLARE @rowsPerPage int,
2. @pageNum int,
3. @startRow int,
4. @endRow int
6. SET @rowsPerPage = 10
7. SET @pageNum = 3
9. SET @startRow = ((@pageNum- 1) * @rowsPerPage)+1
10. SET @endRow = @startRow + @rowsPerPage -1
12. SELECT * FROM (
13. SELECT row_number() OVER (ORDER BY id) as resultNum,
14. id FROM myTable
15. ) as numberResults
16. WHERE resultNum BETWEEN @startRow AND @endRow
    

Method2:

1. SET @rowsPerPage = 10
2. SET @pageNum = 3
4. With SQLPaging
5. As
6. (
7. Select Top(@rowsPerPage * @pageNum) ROW_NUMBER() OVER (ORDER BY id) as resultNum, id
8. FROM myTable
9. )
10. select * from SQLPaging where resultNum > ((@pageNum - 1) * @rowsPerPage)