Mostly we are looking for specific range of record from database instead of fetching all the records from the database to increase scalability of our web-application .
Below examples will describes how we can archive pagination using “Sql queries” / “Stored Procedure”
Example :
CREATE TABLE [dbo].[Employee](
[Id] [int] NOT NULL PRIMARY KEY,
[Name] [varchar](50) NULL,
[Age] [int] NOT NULL,
[Photo] [image] NULL,
[Salary] [numeric](10, 2) NULL,
)
INSERT INTO Employee values (101,’James Clerk’,29,NULL,1000.00);
INSERT INTO Employee values (102,’Steve Proell’,40,NULL,60000.00);
INSERT INTO Employee values (103,’Matt Mcnair’,35,NULL,5000.00);
INSERT INTO Employee values (104,’Amit Kr’,29,NULL,200.00);
INSERT INTO Employee values (105,’Jeff Yeary’,32,NULL,45000.00);
INSERT INTO Employee values (106,’Lisa Miller’,34,NULL,55000.00);
INSERT INTO Employee values (107,’Eric Pressly’,45,NULL,80000.00);
INSERT INTO Employee values (108,’Brianna Greene’,32,NULL,40000.00);
#Method 1 : ROW_NUMBER()
//Procedure
CREATE PROC GetRecord
(
@startInd int,
@endInd int
)
AS
BEGIN
SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER (ORDER BY SALARY DESC) EMPIND
FROM EMPLOYEE
)employeedata
WHERE EMPIND > @startInd AND EMPIND <= @endInd;
END
@if we want to display record between 2nd to 5th highest salary then execute the procedure as mentioned below.
exec dbo.getRecord 1, 5
#Method 2 : Rank()
SELECT * FROM
(
SELECT *,
RANK() OVER (ORDER BY SALARY DESC) EMPIND
FROM EMPLOYEE
)employeedata
WHERE EMPIND > @startInd AND EMPIND <= @endInd;
#Method 3 : DENSE_RANK()
SELECT * FROM
(
SELECT *,
DENSE_RANK() OVER (ORDER BY SALARY DESC) EMPIND
FROM EMPLOYEE
)employeedata
WHERE EMPIND > @startInd AND EMPIND <= @endInd;
In this post we discussed different methods to archive pagination in “Sql queries” or “Stored Procedure”. Please share your thoughts and others methods to archive pagination.