Pagination is common requirement for any application, where we pull data in chunks depending upon the page number and size of page. Here in this article we will see how we can achieve pagination through Server 2012, now it is very easy to use and implement paging in SQL Server 2012. We only need to know these keywords (OFFSET, FETCH NEXT) with Order By Clause and we this we can start our pagination request.
Lets start our demo, here we are declaring a table
Employee with three fields.
create table Employees (
empid int NOT NULL,
empname varchar(25) NOT NULL,
salary decimal(15,2)
)
Command(s)
completed successfully.
Lets insert few data in the table.
declare @i int = 0
while @i < 10000
begin
insert into Employees
select round(rand() * 10000,0),'abc' + cast(round(rand()*10,0) as varchar), rand()*1000
set @i = @i + 1
end
Now since our table is ready, its time to enter
implement pagination,
declare @offset int = 0
declare @next int = 5
select * from Employees order by empid asc
OFFSET @offset ROWS
FETCH NEXT @next ROWS ONLY
empid empname salary
-----------
------------------------- ---------
1 abc9 364.48
2 abc8 14.74
3 abc10 618.69
4 abc10 852.50
5 abc6 788.89
OFFSET: The query excludes the number of records we
mentioned in OFFSET. It says how many records we need to exclude
FETCH NEXT: It says how many records you want to fetch
from the query.
In our example we use OFFSET as 0, that means we are
not interest in skipping any row and FETCH NEXT is 5 means first five records,
we are interest in.
That’s all one need to do to implement Pagination in
SQL Server 2012.
SQL Server with a Tea glass makes a
perfect combination to deal with darkness :)
Post Reference: Vikram Aristocratic Elfin Share
