How to Limit Rows in a SQL Server ResultSet

In this tutorial, we will learn how to Limit Rows in a SQL Server ResultSet and can be used for pagination. This will help you optimize if dealing with a large amount of data and use a paginated display in our front-end application. We will be using the OFFSET and FETCH clauses in Microsoft SQL Server.

FreeCode Spot ASP.NET Core EF compilation:

Browse ASP.NET Core Entity Framework

What is Pagination?

-Wikipedia

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 dummy data to be used on our example.

1. Create a database named DemoDB or if you have an existing database, you can use it.
2. 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','freecode@gmail.com')
 ,('2','freecode','spot_2','freecode@gmail.com')  
 ,('3','freecode','spot_3','freecode@gmail.com')  
 ,('4','freecode','spot_4','freecode@gmail.com')  
 ,('5','freecode','spot_5','freecode@gmail.com')  
 ,('6','freecode','spot_6','freecode@gmail.com')  
 ,('7','freecode','spot_7','freecode@gmail.com')  
 ,('8','freecode','spot_8','freecode@gmail.com')  
 ,('9','freecode','spot_9','freecode@gmail.com')  
 ,('10','freecode','spot_10','freecode@gmail.com')  
 ,('11','freecode','spot_11','freecode@gmail.com')  
 ,('12','freecode','spot_12','freecode@gmail.com')  
 ,('13','freecode','spot_13','freecode@gmail.com')  
 ,('14','freecode','spot_14','freecode@gmail.com')

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 » Limit Rows in a SQL Query ResultSet

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

1. 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 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.

1. 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 ONLY
 END
 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

Result

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

Result

Summary

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 Limit Rows in a SQL Server ResultSet. 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.

KEEP CODING!

You can check this article for FreeCode Spot ASP.NET MVC using Dapper compilation.

Browser ASP.NET MVC using Dapper