Nrthugu

I have this simple case of query:

;WITH TEST_CTE AS

(

    SELECT 1 N



    UNION ALL



    SELECT N + 1 

    FROM TEST_CTE

    WHERE N < 50

)

SELECT N FROM TEST_CTE 

ORDER BY N

OFFSET 10 ROWS 

FETCH NEXT 5 ROWS ONLY

What does keyword ONLY mean here:

FETCH NEXT 5 ROWS ONLY

Why is it necessary to have it there?

If we check the syntax of the ORDER BY clause when OFFSET is used:

<offset_fetch> ::=  

{   

    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }  

    [  

      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY  

    ]  

}  

You can see that if you need only to OFSET rows, you do not need to specify how many rows to FETCH (you are getting all of the rest). But if you want to get particular amount of rows, the ONLY keyword is a must.

I try to find where exactly in the SQL standard this is defined, because in many cases this is just the standard - it may look as something that is not needed, but this is the standard and people are trying to follow it. So, from here:

Since ISO SQL:2008 results limits can be specified as in the following
example using the FETCH FIRST clause.

SELECT * FROM T FETCH FIRST 10 ROWS ONLY

This clause currently is supported by CA DATACOM/DB 11, IBM DB2, SAP
SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB version 2.0, Oracle 12c
and Mimer SQL.

Microsoft SQL Server 2008 and higher supports FETCH FIRST, but it is
considered part of the ORDER BY clause. The ORDER BY, OFFSET, and
FETCH FIRST clauses are all required for this usage.

SELECT * FROM T ORDER BY acolumn DESC OFFSET 0 ROWS FETCH FIRST 10
ROWS ONLY

You can see, that the ONLY keyword seems to be part of the standard definition. Below is a table where some implemented a syntax not following the standard:

So, in case of SQL Server the only keyword is a must when you are using ORDER BY and FETCH. This seems to be following the standard, so do not waste too much time to worry about it.

The OFFSET FETCH is used for pagination purpose ie; to retrieve only some rows as specified

 SELECT N FROM TEST_CTE 

 ORDER BY N

 OFFSET 10 ROWS 

 FETCH NEXT 5 ROWS ONLY

So here it won't return the first 10 rows when ordering by n because it is specified as the offset . Then it will return the remaining 5 rows only

ONLY is used to specify the rows needed.
If it is not specified then the query will skip the first 10 rows from the sorted result set and return the remaining rows.

If you only want to offset the rows the use OFFSET keyword only.
If you need to limit the number of rows returned then use FETCH with ONLY

DEMO