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