locked
Should set NO COUNT ON be used RRS feed

  • Question

  • I read an article that said setting NOCOUNT ON would improve performance for stored procedures, triggers, and functions.  I don't currently have a need for any of my stored procedures to return the number of rows affected to my application.  

    Is this commonly used to improve performance?  What do you guys think about this?  

    Thursday, January 13, 2011 12:28 AM

Answers

  • We use this in stored procedures and triggers (when don't forget). It may improve performance - less output.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, January 13, 2011 1:16 AM
  • I don't set NOCOUNT ON as a general practice, but I under normal circumstances, I prefer to see the number of affected rows.

    In my experience, there are two reasons to SET NOCOUNT ON:

    1. In stored procedures (and triggers?) that execute SELECT statements that don't return rows to the client. Because if NOCOUNT is turned off, the client will interpret the "rows selected" as the result set.

    2. If you have a cursor or other procedural loop that will perform many queries. If you run this cursor / loop from a client, there will be a roundtrip for each such result. That will slow down the execution considerably.


    Gert-Jan
    • Marked as answer by Ai-hua Qiu Friday, January 21, 2011 6:12 AM
    Thursday, January 13, 2011 6:54 PM

All replies

  • Not pretty much sure about performance because rowcount is always calculated. Its displayed based on the nocount setting.
    When receordset is received from server using ADO or VB etc, <x> rows affected would be a recordset and may break application.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Thursday, January 13, 2011 1:02 AM
  • We use this in stored procedures and triggers (when don't forget). It may improve performance - less output.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, January 13, 2011 1:16 AM
  • We use this in stored procedures and triggers (when don't forget). It may improve performance - less output.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    Less output to client makes sense.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Thursday, January 13, 2011 1:18 AM
  • I don't set NOCOUNT ON as a general practice, but I under normal circumstances, I prefer to see the number of affected rows.

    In my experience, there are two reasons to SET NOCOUNT ON:

    1. In stored procedures (and triggers?) that execute SELECT statements that don't return rows to the client. Because if NOCOUNT is turned off, the client will interpret the "rows selected" as the result set.

    2. If you have a cursor or other procedural loop that will perform many queries. If you run this cursor / loop from a client, there will be a roundtrip for each such result. That will slow down the execution considerably.


    Gert-Jan
    • Marked as answer by Ai-hua Qiu Friday, January 21, 2011 6:12 AM
    Thursday, January 13, 2011 6:54 PM