Paging with Sqlserver2005

Some time we need to implement the paging concept for our web and windows application. To achieve this we generally do very completed code or we have to do the paging with our controls such as Datagrid, Gridview etc. However this control s may affect the performance of our application. For example if there are 1000000 records and if we implement the paging then we have to do the server site code or if we fetch whole the record from database to the application then it will affect the performance, and if we use web application then we have to very much care about the performance of the application. But don’t worry friends there a very simple procedure to implement the paging through the database. We can filter the recodes in our procedure and then we can fetch only those records what we need.

1. Using the Employees table of northwind

2. Now create a procedure

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Pankaj Saha

-- Create date:

-- Description: To fetch the records by paging

-- =============================================

CREATE PROCEDURE GetEmployees

@CurrentPageNo int

,@PageSize int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

WITH EmployeeCTE

AS

(

select *, ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNumber FROM Employees

)

SELECT * FROM EmployeeCTE WHERE RowNumber between ((@CurrentPageNo-1)*@PageSize )+ 1and (@CurrentPageNo*@PageSize-1)

END

GO

3. Execute the following statement

execute GetEmployees 1 ,10

The output would show the top 10 employees information, if you run

execute GetEmployees 2 ,10

The output would be the next 10 records.