A More Efficient Method for Paging in SQL Server stored procedure

Hello guys,

If you have large result sets in your database and want to build an efficient paging through SQL Server stored procedure, I recommend the below article for you.
It's tested and gave good results.

http://www.4guysfromrolla.com/webtech/042606-1.shtml

Note: in this article he works on table Id but if you want to use other column just put a non-clustered index on it and use as below

CREATE  PROCEDURE [dbo].[usp_PageResults_NAI] 
(
    @startRowIndex int,
    @maximumRows int
)
AS

DECLARE @firstColumnDate datetime, @startRow int
 
SET ROWCOUNT @startRowIndex
SELECT @firstColumnDate = DateAdded FROM employees ORDER BY DateAdded

SET ROWCOUNT @maximumRows

SELECT e.*, d.name as DepartmentName 
FROM employees e
WHERE DateAdded>= @firstColumnDate
ORDER BY e.DateAdded
SET ROWCOUNT 0

GO 

I hope it'll help you :)
Best Regards.
Ahmed Gamal