SET ROWCOUNT causes SQL Server to stop processing the query after the specified number of rows are returned.
Once the ROWCOUNT is set then the following queries would return those many number of rows in the resultset irrespective of any filtering or conditions applied.
To turn off this option, set the ROWCOUNT to 0. This command can be executed by any
users. The user need not have any special permissions to execute this command.
SET ROWCOUNT <number> | <@number_var>
It is the best way to optimize a query, if we are aware of how many records we would
need at a time. For example, if you are performing custom paging for GridView in
asp.net 2.0, then you can set the query to return only the required number of rows as set in the PageSize property of the GridView.
Visit MSDN for more details with examples: MSDN