ROW_COUNT option

Function

Limits the number of rows returned from a query.

Allowed values

Integer.

Default

0 (no limit on rows returned)

Scope

DBA permissions are not required to set this option. Can be set temporary for an individual connection or for the PUBLIC group. Takes effect immediately.

Description

When this runtime option is set to a nonzero value, query processing stops after the specified number of rows.

This option affects only statements with the keyword SELECT. It does not affect UPDATE and DELETE statements.

The SELECT statement keywords FIRST and TOP also limit the number of rows returned from a query. Using FIRST is the same as setting the ROW_COUNT database option to 1. Using TOP is the same as setting the ROW_COUNT option to the same number of rows. If both TOP and ROW_COUNT are set, then the value of TOP takes precedence.

The ROW_COUNT option could produce non-deterministic results when used in a query involving global variables, system functions or proxy tables. Such queries are partly executed using CIS (Component Integrated Services). In such cases, use SELECT TOP n instead of setting ROW_COUNT, or set the global variable to a local one and use that local variable in the query.

See also

“QUERY_ROWS_RETURNED_LIMIT option”

SELECT statement