SET ROWCOUNT

---

You may limit the number of rows returned by all subsequent SELECT statements within the current session by using the keyword SET ROWCOUNT.

The main difference between SET ROWCOUNT and TOP is that TOP affects only the current statement, while SET ROWCOUNT affects all statements issued during the current database session, until the next SET ROWCOUNT or until the session is terminated.

Syntax

SET ROWCOUNT = number  

Remarks

If a SELECT statement subject to a SET ROWCOUNT condition contains an ORDER BY clause and an index cannot be used to optimize on the ORDER BY clause, the SRDE generates a temporary table. The temporary table is populated with the query's entire result set. The rows in the temporary table are ordered as specified by the ORDER BY clause and the ROWCOUNT number of rows in the ordered result set are returned.

You may turn off the ROWCOUNT feature by setting ROWCOUNT to zero:

SET ROWCOUNT = 0 

SET ROWCOUNT is ignored when dynamic cursors are used.

If both SET ROWCOUNT and TOP are applied to a given query, the number of rows returned is the lower of the two values.

Examples

Also see the examples for TOP .

SET ROWCOUNT = 10; 
SELECT * FROM person; 
			-- returns 10 rows 

See Also

TOP