How to stop SQL Server Query processing after number of rows to be returned?

Problem: How to stop SQL Server Query processing after number of rows to be returned?

Solution:  ROWCOUNT

In some cases, where infinite searches occur we need to stop the query processing for several reasons. If we must break the query after several rows are processed to set a limit use ROW COUNT on Top of the query.

To set this option off so that all rows are returned, specify SET ROWCOUNT 0.

Setting the SET ROWCOUNT option causes most Transact-SQL statements to stop processing when they have been affected by the specified number of rows. This includes triggers. The ROWCOUNT option does not affect dynamic cursors, but it does limit the rowset of keyset and insensitive cursors. This option should be used with caution.

SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.

The setting of SET ROWCOUNT is set at execute or run time and not at parse time.

Example:

In this example, I would like to show we can limit the number of rows returned in the select statement.

SET ROWCOUNT 10

SELECT * from dbo.spt_values

SET ROWCOUNT 0

SELECT * from dbo.spt_values

Results: