sql-servernocount

What are the advantages and disadvantages of turning NOCOUNT off in SQL server queries?

What are the advantages and disadvantages of turning NOCOUNT off in SQL server queries? ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­

Solution

  • 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