Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
MS SQL Server
2.
OFFSET-FETCH in MS SQL Server
2.1.
OFFSET CLAUSE
2.2.
FETCH CLAUSE
2.3.
Syntax
3.
Example of OFFSET-FETCH in MS SQL Server
3.1.
Code
3.1.1.
Output
3.2.
Explanation
3.3.
Code
3.3.1.
Output
3.4.
Explanation
4.
Example for OFFSET Clause Only
4.1.
Code
4.1.1.
Output
4.2.
Explanation
5.
Example for FETCH Clause 
5.1.
Code
5.1.1.
Output
5.2.
Explanation
6.
Example for OFFSET-FETCH clause in MS SQL Server
6.1.
Code
6.1.1.
Output
6.2.
Explanation
7.
Example of Using OFFSET-FETCH Clause with Dynamic Values
7.1.
Code
7.1.1.
Output
7.2.
Explanation
8.
TOP Clause
8.1.
SYNTAX
8.2.
Code
8.2.1.
Output
8.3.
Explanation
8.4.
Code
8.4.1.
Output
8.5.
Explanation
9.
Advantages of OFFSET-FETCH
10.
Disadvantage of OFFSET-FETCH
11.
Different Between TOP and OFFSET-FETCH
12.
Frequently Asked Questions
12.1.
Does the OFFSET-FETCH clause impact query performance?
12.2.
Can we combine multiple OFFSET-FETCH clauses in a single query?
12.3.
What are the alternatives to the OFFSET-FETCH clause for pagination in MS SQL server?
12.4.
What will happen if you specify a negative value for the FETCH value in the OFFSET-FETCH clause?
12.5.
What is the purpose of the Offset-Fetch clause in the MS SQL server?
13.
Conclusion
Last Updated: Mar 27, 2024
Easy

Offset-Fetch in MS SQL Server

Author Vidhi Sareen
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Hey Ninjas! Consider a scenario where a support team must review and address customer inquiries. To reply to these inquiries, the team needs to search the entire database of each request, which is time-consuming and complex to solve.

Offset-Fetch in MS SQL Server

Solving this problem involves determining the range of rows required to address the issue. We can use the OFFSET clause to limit the number of rows returned. OFFSET-FETCH feature is a solution for retrieving a record with limited memory and avoiding an out-of-memory exception. This article will discuss different aspects related to Offset-Fetch in MS SQL Server.

MS SQL Server

MS SQL Server is a Relational Database Management System(RDBMS). Microsoft developed it. MS SQL Server is widely used as a database management tool because storing and retrieving data based on specific requirements is easy. The designers mainly designed MS SQL Server for the Windows environment. It provides more security features like data encryption etc. 

OFFSET-FETCH in MS SQL Server

Pagination is a common feature that has functionality for users to navigate through result pages for a search or directly jump to a specific page. Pagination refers to reducing the number of records from the result set. Paginated Content is easy to navigate and understand. Pagination is an effective way to make our massive content data more manageable. If we do not use pagination in our huge Content, it may cause more hindrance than help.

example

In an SQL server, you can achieve pagination using the OFFSET and FETCH clauses, which are used with ORDER BY clauses. Users mainly use it to divide large data into smaller parts. Web applications make extensive use of it. It was introduced in SQL Server 2012. This article will educate you on how to use the Offset-Fetch clause in MS SQL Server. 

OFFSET CLAUSE

Users use it to specify the row number from which the query should start returning results after skipping a certain number of rows. It's compulsory to use it, and its value must be greater than zero; otherwise, it will return an error or an exception.

FETCH CLAUSE

The clause specifies the number of rows to return after using the OFFSET clause. It is optimal to use. We cannot use it without an OFFSET clause. 

Following is the syntax used for OFFSET and FETCH clauses.

Syntax

SELECT 
  * 
FROM 
  Table_name 
ORDER BY 
  column_list [ASC | DESC] OFFSET rows_to_skip ROWS FETCH [FIRST | NEXT] rows_to_fetch ROWS ONLY

 

  • Table_name - the name of the table that the user wants to create.
  • ORDER BY - display tables in ascending or descending order.
  • OFFSET - used to skip the number of records.
  • FETCH - determines the number of rows that need to be retrieved.

 

And Recursive Relationship in DBMS

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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

output

Explanation

In this table, We created two columns named 'Student_IDand '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

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.

example

Code

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

Output

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.

example

Code

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

Output

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.

example

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

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

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

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

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

TOP  OFFSET-FETCH
It returns a specific number of rows from the resultant table  It allows pagination by specifying the starting and number of rows to fetch
SELECT TOP value col1, col2 SELECT col1, col2 FROM table_name ORDER BY col OFFSET offset_value Rows FETCH NEXT fetch_value ROWS ONLY
It retrieves a specific set of rows. It fetches subsets of rows from a particular position.
It supports earlier versions of MS SQL Server. It is supported in MS SQL Server 2012 and available in the latest version.
You can use it with or without ORDER BY in the table. Experts recommend using it with the ORDER BY clause.

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.

Previous article
MySQL REGEXP operator
Next article
Reset MySQL Root Password
Live masterclass