Limiting the Size of the Result Set

The row_count limit type limits the number of rows returned to the user. A limit violation occurs when the number of rows returned by a select statement exceeds the limit value.

If the resource limit issues a warning as its action, and a query exceeds the row limit, the full number of rows are returned, followed by a warning that indicates the limit value; for example:

Row count exceeded limit of 50.

If the resource limit’s action aborts the query batch or transaction or kills the session, and a query exceeds the row limit, only the limited number of rows are returned and the query batch, transaction, or session aborts. SAP ASE displays a message similar to:

Row count exceeded limit of 50.
Transaction has been aborted.

The row_count limit type applies to all select statements at execution. You cannot limit an estimated number of rows returned at preexecution time.

Use the @@rowcount global variable to help develop appropriate limits for row count. Selecting this variable after running a typical query can tell you how many rows the query returned.

A row count limit applies to the cumulative number of rows that are returned through a cursor from the time the cursor opens to the time it closes. The optimizer recalculates the row_count limit each time a cursor opens.