To NOCOUNT or not to NOCOUNT .. that is the question

Well, there has been an ongoing debate whether it is good practice to use "SET NOCOUNT ON" in SQL Server to reduce the network traffic.

I think if you are running UPDATE and DELETE statements or stored procedures where you don't expect any results back or have output parameters that contain the outcome of the stored procedure, it probably makes sense to include it.

There's a post by Jon Galloway that looks at this as well and argues that the RecordsAffected is hardly used anymore in ADO.net and with SqlDataReaders.
So the bottom line is: use SET NOCOUNT ON if you don't need to have the RecordsAffected as part of your business logic.