In this tutorial, we will learn how we can create a Server-Side Query Pagination in SQL Server. This will help you optimize if dealing with a large amount of data and display it in your application. We will be using the OFFSET and FETCH clauses in Microsoft SQL Server.
What is Pagination?
This is used in some form in almost every web application to divide returned data and display it on multiple pages within one web page. This also includes the logic of preparing and displaying the links to the various pages. Pagination can be handled client-side or server-side.
Paging can be done on the front-end, back-end, or both. Most of the time if you are dealing with a large amount of data front-end pagination will not be enough since a large amount of data will take time to request to a server. Most of the time causes a time-out error or even makes your application crash and unresponsive.
This is where we need server-side pagination, the concept would be to deliver the data to the front-end in chunks or in pages. This means that instead of selecting and returning all the data that we need on the front-end we will divide it into pages. To see this in action let’s go over the steps below.
I. Create Database Table
Let’s create a sample table where we can add a dummy data to be used on our example.
- Create a database named DemoDB or if you have an existing database, you can use it.
- Run this command in a query window to create a customer table
CREATE TABLE customer ( CustomerId INT NOT NULL, FirstName VARCHAR(40) NOT NULL, LastName VARCHAR(20) NOT NULL, Email VARCHAR(60) NOT NULL CONSTRAINT PK_Customer PRIMARY KEY (CustomerId) );
3. Now, let’s add our dummy customer data. To do that, you can manually add it using the Management SQL Server Manager or simply copy the SQL Query below and then execute it on a New Query on your SQL Editor.
INSERT INTO customer(CustomerID,FirstName,LastName,Email) VALUES ('1','freecode','spot_1','[email protected]') ,('2','freecode','spot_2','[email protected]') ,('3','freecode','spot_3','[email protected]') ,('4','freecode','spot_4','[email protected]') ,('5','freecode','spot_5','[email protected]') ,('6','freecode','spot_6','[email protected]') ,('7','freecode','spot_7','[email protected]') ,('8','freecode','spot_8','[email protected]') ,('9','freecode','spot_9','[email protected]') ,('10','freecode','spot_10','[email protected]') ,('11','freecode','spot_11','[email protected]') ,('12','freecode','spot_12','[email protected]') ,('13','freecode','spot_13','[email protected]') ,('14','freecode','spot_14','[email protected]')
4. Once, you have successfully run the command above on step 3.Verify the data by selecting the customer table.
Select * FROM Customer
You may refer to the image attached below. This image is the final sample data that I add to perform this tutorial.
II. Query A paginated Result
Now, let’s create the actual paginated query. But before that let’s understand how OFFSET and FETCH works.
- OFFSET » This argument is used to identify the starting point to return rows from a result set.
- FETCH » This command retrieves a number of records from the OFFSET number which is the starting point.
For example, we want to get the first 7 records on the customer table. All we need is to set the starting point which is OFFSET and then set the FETCH command. See the code snippet below.
Select * from customer Order By customerid OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY;
Now, we need to create a formula that we can use to dynamically query a result per page. Since we have a total of 14 records on the customer table we can divide them into two pages. Both pages will contain 7 records. This is how I constructed my query. I have used two parameters a page number and a Page Size.
- PageNumber » Number of pages we want to result.
- PageSize » Max number of records per pages
Below is the final formula for this query.
OFFSET (PageNumber-1) * PageSize
FETCH NEXT PageSize ROWS ONLY
- Let’s try it in an actual query for the page 1. You can execute it yourself. See the code snippet below.
Select * from customer Order By customerid OFFSET (1 - 1) * 7 ROWS FETCH NEXT 7 ROWS ONLY;
This code will result to this. The OFFSET value would be zero and the FETCH value is seven.
2. Now, try to query the page 2. This query should return the rows from row 8 to row 14.
Select * from customer Order By customerid OFFSET (2 - 1) * 7 ROWS FETCH NEXT 7 ROWS ONLY;
The query above will set the OFFSET value to seven and the FETCH value remains seven. This will return the records with customer ID 8 – 14 which is the last record on our sample data.
Since we have the calculation on the pagination that we need. We can simply create a SQL function or a stored procedure to make this usable on the front-end query.
III. Create SQL Stored Procedure
In this article, we are going to use a stored procedure. This stored procedure will accept two parameters. A page number and page size. See the code snippet below.
- Open your Microsoft SQL Server the write this stored procedure. See the code snippet below.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE GetRowsByPageNumberAndPageSize @PageNumber INT, @PageSize INT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
Select * from customer
Order By customerid
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLYEND GO
- PageNumber is the parameter for the desired page.
- PageSize is the parameter for the desired number of records per page.
2. Execute the query to create the stored procedure.
3. Execute the stored procedure. You can play with a different value to test the procedure.
- @PageSize » Number of rows to display
- @PageNumber » Number of pages
Page Number = 1
USE [DemoDB] GO DECLARE @return_value int EXEC @return_value = [dbo].[GetRowsByPageNumberAndPageSize] @PageNumber = 1, @PageSize = 7 SELECT 'Return Value' = @return_value GO
Page Number = 2
USE [DemoDB] GO DECLARE @return_value int EXEC @return_value = [dbo].[GetRowsByPageNumberAndPageSize] @PageNumber = 2, @PageSize = 7 SELECT 'Return Value' = @return_value GO
FreeCode Spot ASP.NET MVC using Dapper
FreeCode Spot ASP.NET Core EF Cheat Sheet
Dealing with a large amount of data sometimes would really force you to do server-side pagination. This way we can eliminate the longer response time that causes time-out error and even makes your application unresponsive. This tutorial shows how to Create a Server-Side Query Pagination in SQL Server. We use the Fetch and Offset command to filter the select query result. Hopefully, this will give you an idea that will help you optimized your future projects.