Pagination in SQL Server / Selecting a specific range of records from a table or resultset in SQL Server

Hello Friends J,

 

Generally Paging / Pagination concept is used in ASP.Net to display the records in multiple pages instead of displaying all at once and this increases the response time of the web page also.

 

So far this paging is being handled by ADO.Net( I don’t know how it works internally  L) and we used to implement the logic with a complex logic in SQL server earlier

 

But in SQL Server 2005, very easyly we can apply the pagination / select specific range of records from a result seet with the help of WITH common table expression and ROW_NUMBERR() which is a ranking function

 

I hope you find this article useful J

 

Click here to download the script

 

Example:-

CREATE TABLE Dept( Dept_id INT PRIMARY KEY, DeptName VARCHAR(100), Loc VARCHAR(100))

GO

 

INSERT INTO Dept VALUES(101,‘Admin’,‘Chennai’)

INSERT INTO Dept VALUES(102,‘Sales’,‘Hyderabad’)

INSERT INTO Dept VALUES(103,‘Operations’,‘Delhi’)

INSERT INTO Dept VALUES(104,‘IT’,‘Banglore’)

 

GO

CREATE TABLE Employee(Emp_id INT PRIMARY KEY, EmpName VARCHAR(100),DOJ DATETIME, Salary MONEY, Dept_id INT REFERENCES Dept(Dept_id))

 

GO

INSERT INTO Employee VALUES(1001,‘Chandra’,‘15-Dec-2004′,10000,104)

INSERT INTO Employee VALUES(1002,‘VVS’,‘01-Jan-2005′,30000,104)

INSERT INTO Employee VALUES(1003,‘Pagalavan’,‘15-Oct-2005′,40000,104)

INSERT INTO Employee VALUES(1004,‘ASN’,‘10-Feb-2006′,20000,104)

INSERT INTO Employee VALUES(1005,‘Sathish’,‘19-Dec-2005′,10000,104)

INSERT INTO Employee VALUES(1006,‘Sri’,‘06-Sep-2007′,50000,104)

INSERT INTO Employee VALUES(1007,‘Sangeetha’,‘10-Nov-2007′,15000,104)

INSERT INTO Employee VALUES(1008,‘Manick’,‘25-Jan-2006′,20000,104)

INSERT INTO Employee VALUES(1009,‘Kanchana’,‘15-Feb-2005′,19000,104)

INSERT INTO Employee VALUES(1010,‘Arun’,‘15-Mar-2005′,12000,103)

INSERT INTO Employee VALUES(1011,‘Zahed’,‘05-Apr-2004′,11000,103)

INSERT INTO Employee VALUES(1012,‘Rajesh’,‘14-May-2005′,60000,103)

INSERT INTO Employee VALUES(1013,‘Ila’,‘15-Jun-2006′,80000,102)

INSERT INTO Employee VALUES(1014,‘Remya’,‘15-Jul-2006′,40000,102)

INSERT INTO Employee VALUES(1015,‘Tito’,‘15-Aug-2008′,70000,102)

INSERT INTO Employee VALUES(1016,‘RP’,‘11-Sep-2007′,30000,102)

INSERT INTO Employee VALUES(1017,‘Khiroj’,‘18-Oct-2008′,40000,102)

INSERT INTO Employee VALUES(1018,‘Gopi’,‘20-Nov-2005′,60000,101)

INSERT INTO Employee VALUES(1019,‘Venkat’,‘12-Dec-2007′,10000,101)

INSERT INTO Employee VALUES(1020,‘JJR’,‘12-Jan-2006′,15000,101)

INSERT INTO Employee VALUES(1021,‘Shivan’,‘15-Feb-2008′,25000,101)

  

GO

/*Displays Employee records ranging from 11 to 20*/

          WITH EmpDetails AS

          (

          SELECT *, ROW_NUMBER() OVER (ORDER BY Emp_id DESC) AS RowNum FROM Employee

          )

          SELECT * FROM EmpDetails WHERE RowNum BETWEEN 11 AND 20

 

 

/*Displays Top two employees who are getting highest salary in each department*/

         

          WITH EmpDetails AS

          (

          SELECT *, ROW_NUMBER() OVER (PARTITION BY Dept_id ORDER BY Salary DESC) AS RowNum FROM Employee

          )

          SELECT * FROM EmpDetails WHERE RowNum BETWEEN 1 AND 2