tkaragiris posted on January 13, 2012 14:12
Here is some SQL code for paging data from a large tables. This code is handy for when you want to present data from a large table in a gridview, but want to do it page by page to improve database performance.
The code is set to take two parameters:
@PageNumber - the page number you want
@PageSize - the number of rows returned for each page
declare @pagenum int
declare @pagesize int
set @pagenum = 1
set @pagesize = 20
SELECT *
FROM (SELECT
PostId,
UserID,
Username,
ForumID,
DateAdded ,
[Subject],
Body,
IPAddress,
ROW_NUMBER() OVER ( ORDER BY Posts.DateAdded DESC) RowNum
FROM
NTFORUMS_POSTS Posts
WHERE
UserID = 6
) ER
WHERE
RowNum between 1 + ((@PageNum - 1) * @PageSize)
AND (@PageNum * @PageSize)