Set ROWCOUNT statement limits the number of records returned to the client. The syntax is:
SET ROWCOUNT 10
SELECT * FROM SalesLT.Customer
The above query will retrieve 10 rows from the Customer table.
To turn off this option, we have to specify SET ROWCOUNT 0
SET ROWCOUNT is ignored for INSERT, UPDATE, and DELETE statements against remote tables and local, remote partitioned views. 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.
From SQL Server 2005 onward, it is recommended to use TOP (n) instead of SET ROWCOUNT n