OFFSETFETCH

OFFSETFETCH refers to a SQL clause pattern used to paginate query results by skipping a number of rows (OFFSET) and returning a limited set of rows (FETCH NEXT or FETCH FIRST). It is part of the standard approach to paging in many modern relational database systems and is commonly used in conjunction with an ORDER BY clause, which determines the ordering of the rows before the offset and fetch are applied.

In practice, the typical syntax in SQL Server and other compliant systems is: ORDER BY some_column OFFSET offset ROWS FETCH NEXT fetch_count ROWS ONLY. The OFFSET value can be zero, and FETCH NEXT must be a positive integer. The ORDER BY clause is required for OFFSET-FETCH to work, as the rows to skip and return depend on a defined order. Some databases implement similar paging using alternative constructs such as LIMIT and OFFSET, or require a ROW_NUMBER() window function, if they do not support the standard OFFSET-FETCH syntax.

Example: SELECT id, name FROM employees ORDER BY hire_date DESC OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY; This skips the first 50 rows in the ordered result and returns the next 25.

Compatibility and notes: OFFSET-FETCH is supported in SQL Server starting with 2012 and is part of the SQL standard, though some databases offer equivalent functionality with different syntax. When paging large datasets, performance can be affected by a high OFFSET value, since the database may still scan the preceding rows. Alternative approaches include keyset pagination or maintaining a stable sort key to enable more efficient paging.

See also: LIMIT/OFFSET, ROW_NUMBER, pagination strategies.