SQL - Pagination

SQL - Pagination
• SQL - Pagination
Pagination in SQL Server
Pagination is a process that is used to divide large data into smaller discrete pages, and this process is also known as paging. Pagination is commonly used by web applications and can be seen on Google. When we search for something on Google, it shows the results on a separate page; this is the main idea of the pagination.
What is Pagination in SQL Server?
In terms of the SQL Server, the aim of the pagination is, dividing a resultset into discrete pages with the help of the query. When the OFFSET and FETCH arguments are used in with the ORDER BY clause in a SELECT statement, it will be a pagination solution for SQL Server.
OFFSET argument specifies how many rows will be skipped from the resultset of the query. In the following example, the query will skip the first 10 rows of the EmployeeDetail table and then return all remaining rows.
select * from EmployeeDetail
ORDER BY employee_id
OFFSET 10 ROWS
When we set OFFSET value as 0, no rows will be skipped from the resultset. The following query can be an example of this usage type:
select * from EmployeeDetail
ORDER BY employee_id
OFFSET 0 ROWS
On the other hand, if we set the OFFSET value, which is greater than the total row number of the resultset, no rows will be displayed on the result. When we consider the following query, the EmployeeDetail table total number of the rows is 50, and we set OFFSET value as 40, so the query will not display any result.
FETCH argument specifies how many rows will be displayed in the result, and the FETCH argument must be used with the OFFSET argument. In the following example, we will skip the first 10 rows and then limit the resultset to 15 rows for our sample table.
select * from EmployeeDetail
ORDER BY employee_id
OFFSET 10 ROWS
FETCH NEXT 15 ROWS ONLY
Tip: The TOP CLAUSE limits the number of rows that are returned from the SELECT statement. When we use the TOP clause without ORDER BY, it can be returned to arbitrary results. When we consider the following example, it will return 3 random rows on each execution of the query.
select TOP 15 * from EmployeeDetail
ORDER BY employee_id
As we learned, the OFFSET-FETCH argument requires the ORDER BY clause in the SELECT statement. If we want to implement an undefined order that is like the previous usage of the TOP clause with OFFSET-FETCH arguments, we can use a query that looks like the below:
select * from EmployeeDetail
ORDER BY (SELECT NULL)
OFFSET 10 ROWS
FETCH NEXT 15 ROWS ONLY
Pagination query in SQL Server
After figuring out the answer to “What is Pagination?” question, we will learn how we can write a pagination query in SQL Server. At first, we will execute the following query and will tackle the query:
Declare @pageNumber as Int=1
Declare @pageSize as Int = 8
select * from EmployeeDetail
ORDER BY employee_id
OFFSET (@pageNumber - 1) * @pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY
Pagination in a Loop
In this example, we will learn a query technique that returns all discrete page results with a single query.
Declare @pageNumber as Int=1
Declare @pageSize as Int = 9
Declare @totalRecord as int
select @totalRecord = count(*) from EmployeeDetail
Declare @totalPages as int =ceiling(@totalRecord /@pageSize)
if(@totalPages * @pageSize) (LESS THAN) @totalRecord
begin
set @totalPages = @totalPages +1
end
while(@totalPages LESS THAN EQUAL TO @pageNumber)
begin
select * from EmployeeDetail
ORDER BY employee_id
OFFSET (@pageNumber - 1) * @pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY
set @pageNumber =@pageNumber+1
end
For this query, we created a pretty simple formula. At first, we assigned the total row number of the SampleFruit table to the @MaxTablePage variable, and then we divided it into how many rows will be displayed on a page. So, we have calculated the number of pages that will be displayed. However, the calculated value can be a decimal, and for that, we used the CEILING function to round it up to the smallest integer number that is bigger than the calculated number. As a second step, we implemented a WHILE-LOOP and iterated @PageNumber variable until the last page of the number.
SQL(Structured Query Language) Playlist
• SQL Theory Tutorial(ST...
#Subscribe the #Channel #Link :- #bansodetechsoluiotn #ajupgrading www.youtube.co...
IF any #Query or #Doubt #DM on #Instagram :- #bansode_ajay_2102
www.instagram....
LinkedIn Profile
/ aj-upgrading-bansode-t...
Link for slides, code samples, and text version of the video #blogger
bansodetechsol...