<p>What are the advantages and disadvantages of turning <strong><code>NOCOUNT</code></strong> off in SQL server queries?
­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­</p>

From SQL BOL:
SET NOCOUNT ON prevents the sending of
  DONE_IN_PROC messages to the client
  for each statement in a stored
  procedure. For stored procedures that
  contain several statements that do not
  return much actual data,
setting SET
  NOCOUNT to ON can provide a
  significant performance boost
, because
  network traffic is greatly reduced.
See
http://msdn.microsoft.com/en-us/library/ms189837.aspx
for more details.
Also, this article on SQLServerCentral is great on this subject:
Performance Effects of NOCOUNT