MAX_ROWS_LIMIT (SELECT Option)
MAX_ROWS_LIMIT limits the number of rows returned by a query. This is useful if a user is interested in only the first n rows of the results returned.
This option is often used in conjunction with the ROWS_OFFSET (see
ROWS_OFFSET (SELECT Option)). How it works in combination with ROWS_OFFSET, OFFSET, FETCH and the maxRows JDBC/ODBC parameter is shown in examples 2 through 9 at the end of this section.
If this option is not specified, all selected rows are returned.
Operator
SELECT
Syntax
MAX_ROWS_LIMIT=<int>
The <int> argument specifies the maximum number of rows the query is to return.
Remarks
• When SELECT options are specified as part of a subquery or subselect, they might not affect the root-level query execution plan.
• For better performance with row filtering, use OFFSET and FETCH rather than MAX_ROWS_LIMIT and ROWS_OFFSET. The reason is that OFFSET and FETCH are SQL-standard options that are pushed to the data source for pass-through queries. MAX_ROWS_LIMIT and ROWS_OFFSET are TDV-only constructs that always perform filtering in TDV (after a much larger number of rows may have been fetched).
• Refer to the SQL 2008 standard for syntax and usage of OFFSET and FETCH.
Example 1
This is a simple example illustrating syntax.
SELECT {OPTION MAX_ROWS_LIMIT=100} * FROM table1
Example 2
In this example, maxRows is too large to have an effect. MAX_ROWS_LIMIT allows 25 rows beyond those skipped by OFFSET, and ROWS_OFFSET removes the first 10 of those.
Query:
SELECT {OPTION ROWS_OFFSET=10, MAX_ROWS_LIMIT=25} * FROM " + tableName + "
OFFSET 50 FETCH NEXT 40 ROWS ONLY
Example 3
In this example, maxRows is too large to have an effect. MAX_ROWS_LIMIT allows 25 rows beyond those skipped by OFFSET, and ROWS_OFFSET removes the first 10 of those.
Query:
SELECT {OPTION ROWS_OFFSET=10, MAX_ROWS_LIMIT=25} * FROM " + tableName + "
OFFSET 50 FETCH NEXT 12 ROWS ONLY"
Example 4
Query:
SELECT {OPTION ROWS_OFFSET=10, MAX_ROWS_LIMIT=25} * FROM " + tableName + "
OFFSET 50 FETCH NEXT 34 ROWS ONLY
Example 5
In this example, maxRows is too large to have an effect. MAX_ROWS_LIMIT allows 25 rows beyond those skipped by OFFSET.
Query:
SELECT {OPTION MAX_ROWS_LIMIT=25} * FROM " + tableName + "
OFFSET 50 FETCH NEXT 34 ROWS ONLY
Example 6
Query:
SELECT {OPTION MAX_ROWS_LIMIT=25} * FROM " + tableName + "
OFFSET 50 FETCH NEXT 34 ROWS ONLY
Example 7
In this example, maxRows is too large to have an effect. MAX_ROWS_LIMIT allows 25 rows beyond those skipped by OFFSET.
Query:
SELECT {OPTION MAX_ROWS_LIMIT=25} * FROM " + tableName + "
OFFSET 50 ROWS
Example 8
In this example, maxRows is too large to have an effect. ROWS_OFFSET removes the first 10 rows beyond those skipped by OFFSET.
Query:
SELECT {OPTION ROWS_OFFSET=10} * FROM " + tableName + "
OFFSET 50 FETCH NEXT 12 ROWS ONLY
Example 9
In this example, ROWS_OFFSET removes the first 10 rows beyond those skipped by OFFSET, and maxRows allows 10 of the remaining rows to be returned.
Query:
SELECT {OPTION ROWS_OFFSET=10} * FROM " + tableName + "
OFFSET 50 FETCH NEXT 34 ROWS ONLY