Offset-Fetch in MS SQL Server

Example of OFFSET-FETCH in MS SQL Server

Let's take an example to know how the OFFSET-FETCH clause works in MS SQL Server.

Code

//created table
CREATE TABLE Students_REPORT (
    Student_ID INT,
    Student_Name VARCHAR(50)
);

//inserted values
INSERT INTO Students_REPORT (Student_ID, Student_Name)
VALUES
    (1, 'Ninja_1'),
    (2, 'Ninja_2'),
    (3, 'Ninja_3'),
    (4, 'Ninja_4'),
    (5, 'Ninja_5'),
    (6, 'Ninja_6'),
    (7, 'Ninja_7'),
    (8, 'Ninja_8'),
    (9, 'Ninja_9'),
    (10, 'Ninja_10');

//selecting all elements from the table to display
SELECT 
  * 
FROM 
  Students_REPORT;

Output

Explanation

In this table, We created two columns named 'Student_ID' and 'Student_Name.' It consists of 10 details of the table. 

Let's apply OFFSET-FETCH in MS SQL Server to the above table.

Code

SELECT 
  Student_ID, 
  Student_Name 
FROM 
  Students_REPORT 
ORDER BY 
  Student_ID OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY;

Output

Explanation

A range of rows is returned from a table by OFFSET. The OFFSET clause can skip the first two rows from the table, and FETCH NEXT can fetch the next five rows from the resultant table. The OFFSET-FETCH clause obtains a specific subset of rows from a table.

Also read, Natural Join in SQL

Example for OFFSET Clause Only

Now to understand the OFFSET clause, let's take another example. In this example, we will skip the first four student records and return the rest of the detail of the table. We will be using the OFFSET clause only.

Code

//used OFFSET clause to skip rows
SELECT 
  Student_ID, 
  Student_Name 
FROM 
  Students_REPORT 
ORDER BY 
  Student_ID OFFSET 4 ROWS;

Output

Explanation

As we can see, we have used only the OFFSET clause in this table. This clause will help us eliminate the first four rows of the resultant table. It will display the records from the fifth row onwards. It will skip the first four rows. 

Example for FETCH Clause 

Let's look at another example to better understand the FETCH clause in MS SQL Server. In this query below, we will retrieve the student's details in ascending order.

Code

SELECT 
  Student_ID, 
  Student_Name 
FROM 
  Students_REPORT 
ORDER BY 
  Student_ID ASC OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY;

Output

Explanation

As we can observe, we have used the OFFSET and FETCH clause in this table. This OFFSET clause will help us eliminate the first two rows of the resultant table. It will display the records from the third row till the next five rows, as mentioned with the FETCH NEXT clause. It will help us to retrieve data from a specific position in a table.

Example for OFFSET-FETCH clause in MS SQL Server

Let's look at another example to understand the OFFSET-FETCH clause in MS SQL Server. In this query below, we will retrieve the last four student's details in descending order.

Code

//used to sort the table in decreasing order and then used OFFSET and FETCH
SELECT 
  * 
FROM 
  Students_REPORT 
ORDER BY 
  Student_ID DESC OFFSET 0 ROWS FETCH NEXT 4 ROWS ONLY;

Output

Explanation

In this query, we have 'OFFSET 0 ROWS' to specify that no rows need to be skipped and start from the first row (OFFSET 0). We have used 'FETCH NEXT 4 ROWS ONLY' to extract the information. It retrieves data of the following four rows from the sorted table. It will give us the details of the last four students. 

Example of Using OFFSET-FETCH Clause with Dynamic Values

Let's look at another example to understand better how OFFSET - FETCH  in MS SQL Server works. In this query, we will retrieve the records from the table based on the dynamic offset value.

Code

//declare @OffsetValue and @FetchValue dynamically
DECLARE @OffsetValue INT = 2;
DECLARE @FetchValue INT = 6;

//use this variable to skip and fetch values
SELECT 
  * 
FROM 
  Students_REPORT 
ORDER BY 
  Student_ID OFFSET @OffsetValue ROWS FETCH NEXT @FetchValue ROWS ONLY;

Output

Explanation

The above query retrieves records from a table based on a dynamic offset value. @OffsetValue represents the number of rows to skip, and the @FetchValue shows the number of rows fetched after the offset. If the @OffsetValue exceeds a table's total number of rows, the query returns no rows.

TOP Clause

The TOP clause in MS SQL limits the number of rows a query returns. This clause restrains the result set of queries based on the specified number or percentage of rows. Large tables, which consist of lots of data, take more time to return all the records, impacting the database's performance. To solve this problem, we can produce only a specific number of data rows needed from a table using the TOP n clause in the SQL server. The syntax of the TOP n clause is as follows. 

SYNTAX

SELECT 
  TOP value Column1, 
  Column2 
FROM 
  table_name 
WHERE 
  [cond]

Using the TOP syntax, we can determine the number of rows to retrieve from a table, employing a numerical value known as "value." For instance, if we wish to retrieve the first four rows from the table, we can use the expression SELECT TOP 4. This action will yield the top four values.

To understand this better, let's take an example of how the TOP clause works. In this query, we will display the results of 10 students along with their marks.

Code

//create a table
CREATE TABLE Students_report_card (
    Student_ID INT,
    Student_Name VARCHAR(50),
Marks INT
);

//insert values into the table
INSERT INTO Students_report_card(Student_ID, Student_Name, Marks)
VALUES
    (1, 'Ninja_1', 76),
    (2, 'Ninja_2', 89),
    (3, 'Ninja_3', 79),
    (4, 'Ninja_4', 75),
    (5, 'Ninja_5', 87),
    (6, 'Ninja_6', 88),
    (7, 'Ninja_7', 89),
    (8, 'Ninja_8', 90),
    (9, 'Ninja_9', 93),
    (10, 'Ninja_10', 91);

//select all values to display
SELECT 
  * 
FROM 
  Students_report_card;

Output

Explanation

In this table, we can see that there are three columns named 'Student_ID’,’ Student_Name,' and ‘Marks’. This table consists of 10 student records along with the students' marks. 

Now let's apply the TOP clause in this table.

Code

//apply TOP clause to retrieve the top 5 marks students from the table.
SELECT 
  TOP 5 Student_ID, 
  Student_Name, 
  Marks 
FROM 
  Students_report_card;

Output

Explanation

This query will retrieve the top 5 rows from the 'Student_report_card' table having three columns.

Advantages of OFFSET-FETCH

There are many advantages of using OFFSET-FETCH in MS SQL Server:

• It actively incorporates it for smooth pagination.
   
• It is very flexible.
   
• It is very consistent with the ordering of the resultant table.
   
• It simplifies the process of elimination of the rows in a vast dataset.

Disadvantage of OFFSET-FETCH

There are some disadvantages of using OFFSET-FETCH in MS SQL Server:

• Offset-Fetch can only be used with ORDER BY.
   
• Offset-Fetch requires the database engine to process and skip a certain number of rows. It can increase resource usage.
   
• SQL Server versions released before 2012 lack support for OFFSET-FETCH clauses.
   
• There may create problems in data modification. The changes may affect the ordering of the table.

Different Between TOP and OFFSET-FETCH

Also see,  Checkpoint in DBMS

Frequently Asked Questions

Does the OFFSET-FETCH clause impact query performance?

It can impact the performance of large resultant tables as it will process and skip a certain number of rows before fetching. It is time-consuming and requires expert manipulation techniques. 

Can we combine multiple OFFSET-FETCH clauses in a single query?

We can not combine multiple OFFSET-FETCH clauses in a single query. Each query has only one offset-fetch clause to specify the desired subset of rows.

What are the alternatives to the OFFSET-FETCH clause for pagination in MS SQL server?

There are different alternatives of OFFSET-FETCH clauses like the ROW_NUMBER() clause or server-side cursors. They offer flexibility in pagination but have other performance characteristics than OFFSET-FETCH clauses.   

What will happen if you specify a negative value for the FETCH value in the OFFSET-FETCH clause?

It will show an error if you specify a negative FETCH value. The FETCH value must have a positive value or a variable to evaluate a positive integer.

What is the purpose of the Offset-Fetch clause in the MS SQL server?

The Offset-Fetch clause works with the ORDER BY clause to ensure consistent and predictable ordering of the results. We use them to implement pagination because it efficiently retrieves specific subsets of data based on offset and fetch values.

Conclusion

OFFSET-FETCH in MS SQL Server provides a convenient way to implement pagination and retrieves subsets of data from a resultant table. It lets users control the starting position and specify the number of rows fetched. This article taught us that we can implement Offset-Fetch in MS SQL Server. We even explore different aspects of the OFFSET-FETCH clause with the help of an example. We even discuss how pagination uses it. However, we also saw that it has certain limitations, like it may impact performance using a large resultant table and many more in this article. This article even discusses the difference between the TOP and OFFSET-FETCH clauses.

To learn more about this topic, check out the link below-

SQL Server Basics

Difference between sql and nosql

What is Microsoft SQL Management Studio?

SQL Server User-Defined Functions

You can find more informative articles or blogs on our platform. You can also practice more coding problems and prepare for interview questions from well-known companies on your platform, Coding Ninjas Studio.