Limits the result set to a specified number of rows. This functionality of restricting the size of the result set is similar to that of the TOP clause. However, the TOP clause only applies to queries at the statement level, while SET ROWCOUNT is a session-wide parameter and is not statement-specific.

Syntax

SET ROWCOUNT rowcount_number;

Parameter

rowcount_number

Specifies the number of rows to be restricted from the result set. This value must be a constant literal.

Notes

  • The default value of the row countis 0, which means that the number of rows in the result set is not restricted, as shown in the example above. When the row count value is greater than 0, the maximum size of the result set is that of the row count value.
  • You can change the row count value at any point of time in a session. The new value affects the subsequent SELECT statements and stored procedures in the session.
  • SET ROWCOUNT restricts the result set after all the statement level clauses (such as, OFFSET, FETCH, and TOP clauses) have been applied. This implies that after the restriction of OFFSET, FETCH, and TOP clauses on the result set, SET ROWCOUNT further restricts the result set.
  • The SET ROWCOUNT statement has no effect on the following:
    • DDL statements such as CREATE, ALTER, and DROP
    • DML statements such as INSERT, DELETE, and UPDATE
    • SHOW statements
    • Triggers
  • The SET ROWCOUNT statement has no effect on the internal SELECT statements used in stored procedures. However, the overall result set of the stored procedures is restricted by SET ROWCOUNT, as shown in the example above.
  • The SET ROWCOUNT statement affects the overall query result and not the intermediate stages of a query execution as the following:
    • Intermediate result set in a subquery
    • Intermediate result set in views or derived tables
    • Intermediate queries used as set operators such as, UNION, INTERSECT, and MINUS

Examples

SET ROWCOUNT statement

The following examples demonstrate the SET ROWCOUNT statement:

SET ROWCOUNT 10;
SELECT * FROM Customer;

The above query returns the first ten rows in the result set.

SET ROWCOUNT statement with OFFSET and FETCH clauses

The following example demonstrates the SET ROWCOUNT statement with OFFSET and FETCH clauses in the SELECT statement:

SET ROWCOUNT 5;
SELECT * FROM Customer
ORDER BY Name
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

The above query further restricts the result set, after applying the OFFSET and FETCH clauses, and returns rows 11 through 15.

SET ROWCOUNT statement

The following example demonstrates the SET ROWCOUNT statement, where the row count value is set to 3:

SET ROWCOUNT 3;
SELECT TOP 5 * FROM Customer;

The above query returns only the first 3 rows in the result set.

Consider running the same query with the row count value set to 0 (the default value for SET ROWCOUNT), as shown below:

SET ROWCOUNT 0;
SELECT TOP 5 * FROM Customer;

The above query returns the first 5 rows in the result set without restricting any rows.

SET ROWCOUNT statement in a stored procedure

The following example demonstrates the SET ROWCOUNT statement in a stored procedure containing more than one row:

SET ROWCOUNT 1;
CALL get_sal ();

The above query returns only the first row in the result set.