Limits the number of rows returned from a query.
Option can be set at the database (PUBLIC) or user level. When set at the database level, the value becomes the default for any new user, but has no impact on existing users. When set at the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.
Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. Takes effect immediately.
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 and 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 ROW_COUNT 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.