In SQL Server 7.0, every time you execute a query, you receive an additional result set indicating the number of rows processed by your query. Because SQL Server must generate this value each time you run a query, it increases the workload on your server. Turning off this count of rows processed by your query (or stored procedure) can significantly increase the performance of your query. You can turn the count off for your current connection by executing the statement SET NOCOUNT ON. If you want to turn off the count for all connections, you'll need to modify the properties of your server in SQL Server Enterprise Manager.
Source: John Sewell
Viewed 9206 times