OFFSET Command in SQL Server

OFFSET Command in SQL Server

Introduction-

In SQL Server, the OFFSET command is used in conjunction with the ORDER BY clause to specify the number of rows to skip before starting to return rows from a query. This is particularly useful for implementing pagination in applications or when you need to skip a certain number of rows before fetching results.

 

Here's the basic syntax of the OFFSET command-

SELECT column1, column2, ...

FROM table_name

ORDER BY column1, column2, ...

OFFSET {number_of_rows | expression} ROWS

FETCH {FIRST | NEXT} {number_of_rows | expression} ROWS ONLY;

 

Let's break down each part:

OFFSET-

This specifies the number of rows to skip before starting to return rows. It is always used with FETCH to define the number of rows to return.

 

{number_of_rows | expression}-

This can be a numeric value or an expression that evaluates to the number of rows to skip or fetch.

 

ROWS-

This keyword specifies that the value specified after OFFSET and FETCH is in terms of rows.

 

FETCH {FIRST | NEXT}-

This part is used to specify whether to fetch the first or next set of rows after the offset.

 

{number_of_rows | expression} ROWS ONLY-

This part specifies the number of rows to fetch after the offset.

Now let's see an example to understand how

to use the OFFSET command:

 

Suppose we have a table called Employees with columns EmployeeID, FirstName, and LastName.

We want to fetch 5 rows from the table starting from the 6th row, ordered by EmployeeID.

 

SELECT EmployeeID, FirstName, LastName

FROM Employees

ORDER BY EmployeeID

OFFSET 5 ROWS

FETCH NEXT 5 ROWS ONLY;

 

In this example:

OFFSET 5 ROWS-

Skips the first 5 rows.

 

FETCH NEXT 5 ROWS ONLY-

Fetches the next 5 rows after skipping the first 5.

 

This query will return 5 rows from the Employees table starting from the 6th row, ordered by EmployeeID.

Keep in mind that the OFFSET and FETCH clauses are available in SQL Server 2012 and later versions. If you're using an older version, you may need to use alternative methods like ROW_NUMBER () function with subqueries to achieve similar results.