Efficient way to do Paging in SQL Server

I was going through SQLServerCentral.com and found this pretty nice article to do paging in an efficient manner. So thought of sharing with you all and ofcourse it is a note to myself 🙂


WITH Keys
AS
(
 SELECT
 TOP (@PageNumber * @PageSize)
 rn = ROW_NUMBER() OVER (ORDER BY P1.Post_ID ASC)
 P1.Post_ID
 FROM
 dbo.Post P1
 ORDER BY
 P1.Post_ID ASC
),
SelectedKeys AS
(
 SELECT
 Top(@PageSize)
 SK.rn,
 SK.Post_ID
 FROM
 Keys SK
 WHERE
 SK.rn >((@PageNumber -1 ) * @PageSize)
 ORDER BY
 SK.Post_ID ASC
)
SELECT
 SK.rn,
 P2.Post_ID,
 P2.Thread_ID,
 P2.Member_ID,
 P2.Created_Date,
 P2.Title,
 P2.Body
FROM
 SelectedKeys SK
JOIN
 dbo.Post P2
ON
 P2.Post_ID = SK.Post_ID
ORDER BY
 SK.Post_ID ASC

Happy Programming!!!

Cheers,

Raja

Advertisements

0 Responses to “Efficient way to do Paging in SQL Server”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s





%d bloggers like this: