Brent Ozar takes us through a simple but useful SET command:
When you’re working with T-SQL, you’ll often see SET NOCOUNT ON at the beginning of stored procedures and triggers.
What SET NCOUNT ON does is prevent the “1 row affected” messages from being returned for every operation.
Read on to see why this is useful. Also check out the comments for a few other reasons to use it, such as applications written in such a way that they get confused and fail when NOCOUNT is off.