SQL SERVER – Server Side Paging in SQL Server 2005 or 2008

Server Side Paging in SQL Server

In this post i am giving sql server paging example... Basically SQL Server paging is useful for fetching the records from the database as per our requested count. 

Working mechanism: if we want to fetch 100 records, first we have are going to fetch 10 records, next 10 records we have to ask by page 2. . if we give page 5 we are going to fetch 40 to 50 records. 

In the below example first we are creating a table EmployeeTable, after that entering values into table after that creating view and then created stored procedure. This is one of the important sql server interview question

exec spPaging 1,5

--creating the table.
create table dbo.EmployeeTable
(
EmpId int identity primary key,
EmpName varchar(40),
Sal int 
)
go

--inserting data into table
insert into EmployeeTable values('ranga',2000),('rajesh',3000),('kumar',4000),('swapna',5000),('gopi',6000)


--creating view.
create view pagingView
as
select empid,empname,sal from EmployeeTable
go


--creating procedure
CREATE PROCEDURE [dbo].[spPaging]
(
  @PageNumber int,
  @PageRecordsSize int
)
AS
begin
SET NOCOUNT ON;

SELECT TOP(@PageRecordsSize) * FROM 
(
 SELECT Row_ID = ROW_NUMBER() OVER (ORDER BY empid), 
  empid, empname, sal, TotalRows=Count(*) OVER()
 FROM pagingView  
) 
A WHERE A.Row_ID > ((@PageNumber-1)*@PageRecordsSize)
SET NOCOUNT OFF
end
go

exec spPaging 1,5

Output:

 Server Side Paging in SQL Server