What is the usage of the offset in SQL Server?

In SQL Server, the OFFSET is used for pagination, indicating where the query results should start from. The OFFSET clause takes two parameters, the first parameter is the number of rows to skip, and the second parameter is the number of rows to return.

For example, suppose there is a table called “employees” which includes fields like id, name, and age. To retrieve 10 records starting from the 11th row, you can use the OFFSET clause.

SELECT id, name, age
FROM employees
ORDER BY id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

In the above query, OFFSET 10 ROWS means skipping the first 10 rows, and FETCH NEXT 10 ROWS ONLY means returning the next 10 rows of records.

It is important to note that the OFFSET and FETCH clauses are only available in SQL Server 2012 and higher versions. In earlier versions, you can use the ROW_NUMBER function to achieve pagination queries.