Offset-Fetch in MS SQL Server

Offset-Fetch is feature in MS SQL Server. It helps to retrieve subset of rows from result set. It consists of two components: OFFSET and FETCH. The OFFSET clause specifies the number of rows to skip from the beginning of the result set. FETCH clause determines the number of rows to retrieve.

Syntax

SELECT column1, column2, ...
FROM table
ORDER BY column
OFFSET {integer_value} {ROWS | ROWS ONLY}
FETCH {FIRST | NEXT} {integer_value} {ROWS | ROWS ONLY} ONLY;

Explanation of Syntax

SELECT column1, column2, ...:

Specifies the columns to be selected from the table.

FROM table

Specifies the table from which to retrieve the data.

ORDER BY column

Defines the column(s) by which the result set should be sorted.

OFFSET {integer_value} {ROWS | ROWS ONLY}

Specifies the number of rows to skip from the beginning of the result set. The {integer_value} represents the number of rows to skip.

FETCH {FIRST | NEXT} {integer_value} {ROWS | ROWS ONLY} ONLY

Specifies the number of rows to retrieve after the offset. The {integer_value} represents the number of rows to retrieve.

Note

• OFFSET clause must be used with the ORDER BY clause.

• OFFSET value should be a non-negative integer.

• FETCH clause can use either FIRST or NEXT keywords interchangeably.

• {ROWS | ROWS ONLY} clause specifies whether to include the word "ROWS" or "ROWS ONLY" in the syntax. Both are equivalent.

Examples

Let's explore a few examples to understand how Offset-Fetch can be used in MS SQL Server

Example-1: Simple Pagination

Consider a table called "Customers" with columns "CustomerID" and "CustomerName." To fetch the first 10 customers sorted by CustomerID, the following query can be used:

SELECT CustomerID, CustomerName
FROM Customers
ORDER BY CustomerID
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

Example-2: Pagination with Sorting and Filtering

In this example, we want to retrieve the second page of customers, sorted by CustomerName, where the CustomerName starts with "A". The following query can be used

SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerName LIKE 'A%'
ORDER BY CustomerName
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Example-3: Skipping Rows

Write a query to retrieve all customers except the first five. The OFFSET clause allows us to skip the desired number of rows:

SELECT CustomerID, CustomerName
FROM Customers
ORDER BY CustomerID
OFFSET 5 ROWS;

Conclusion

Offset-Fetch in MS SQL Server is a tool for efficient data retrieval and pagination. It simplifies the process of fetching specific ranges of data. Queries became more concise and enhanced performance. By incorporating Offset-Fetch into your database queries, you can optimize data retrieval, improve application responsiveness, and enhance the user experience.

Offset-Fetch is available in MS SQL Server 2012 and later versions. Its flexibility and ease of use. It is a valuable feature for developers and database administrators working with SQL Server. It enables them to efficiently handle large datasets and implement pagination with ease.