SET NOCOUNT ON usage

Question

Inspired by this question where there are differing views on SET NOCOUNT        Should we use SET NOCOUNT ON for SQL Server  If not  why not    What it does Edit 6  on 22 Jul 2011  It suppresses the  xx rows affected  message after any DML  This is a resultset and when sent  the client must process it  It s tiny  but measurable  see answers below   For triggers etc  the client will receive multiple  xx rows affected  and this causes all manner of errors for some ORMs  MS Access  JPA etc  see edits below   Background   General accepted best practice  I thought until this question  is to use SET NOCOUNT ON in triggers and stored procedures in SQL Server  We use it everywhere and a quick google shows plenty of SQL Server MVPs agreeing too   MSDN says this can break a  net SQLDataAdapter   Now  this means to me that the SQLDataAdapter is limited to utterly simply CRUD processing because it expects the  n rows affected  message to match  So  I can t use    IF EXISTS to avoid duplicates  no rows affected message  Note  use with caution WHERE NOT EXISTS  less rows then expected Filter out trivial updates  eg no data actually changes  Do any table access before  such as logging  Hide complexity or denormlisation etc   In the question marc s  who knows his SQL stuff  says do not use it  This differs to what I think  and I regard myself as somewhat competent at SQL too    It s possible I m missing something  feel free to point out the obvious   but what do you folks out there think   Note  it s been years since I saw this error because I don t use SQLDataAdapter nowadays   Edits after comments and questions   Edit  More thoughts     We have multiple clients  one may use a C  SQLDataAdaptor  another may use nHibernate from Java  These can be affected in different ways with SET NOCOUNT ON   If you regard stored procs as methods  then it s bad form  anti-pattern  to assume some internal processing works a certain way for your own purposes   Edit 2  a trigger breaking nHibernate question  where SET NOCOUNT ON can not be set   and no  it s not a duplicate of this   Edit 3  Yet more info  thanks to my MVP colleague   KB 240882  issue causing disconnects on SQL 2000 and earlier Demo of performance gain    Edit 4  13 May 2011  Breaks Linq 2 SQL too when not specified   Edit 5  14 Jun 2011  Breaks JPA  stored proc with table variables  Does JPA 2 0 support SQL Server table variables   Edit 6  15 Aug 2011  The SSMS  Edit rows  data grid requires SET NOCOUNT ON  Update trigger with GROUP BY  Edit 7  07 Mar 2013  More in depth details from  RemusRusanu  Does SET NOCOUNT ON really make that much of a performance difference

User · Answer

SET NOCOUNT ON    This line of code is used in SQL for not returning the number rows affected in the execution of the query  If we don t require the number of rows affected  we can use this as this would help in saving memory usage and increase the speeed of execution of the query

User · Answer

SET NOCOUNT ON- It will show  quot Command s  completed successfully quot   SET NOCOUNT OFF- it will show  quot  No  Of row s  affected  quot

User · Answer

SET NOCOUNT ON even does allows to access to the affected rows like this   SET NOCOUNT ON  DECLARE  test TABLE  ID int   INSERT INTO  test VALUES  1   2   3   DECLARE  affectedRows int   -99    DELETE top  1    FROM  test SET  affectedRows     rowcount  SELECT  affectedRows as affectedRows   Results     affectedRows      1   Messages     Commands completed successfully       Completion time  2020-06-18T16 20 16 9686874 02 00

User · Answer

Regarding the triggers breaking NHibernate  I had that experience first-hand  Basically  when NH does an UPDATE it expects certain number of rows affected  By adding SET NOCOUNT ON to the triggers you get the number of rows back to what NH expected thereby fixing the issue  So yeah  I would definitely recommend turning it off for triggers if you use NH    Regarding the usage in SPs  it s a matter of personal preference  I had always turned the row count off  but then again  there are no real strong arguments either way   On a different note  you should really consider moving away from SP-based architecture  then you won t even have this question

User · Answer

It took me a lot of digging to find real benchmark figures around NOCOUNT  so I figured I d share a quick summary    If your stored procedure uses a cursor to perform a lot of very quick operations with no returned results  having NOCOUNT OFF can take roughly 10 times as long as having it ON  1 This is the worst-case scenario  If your stored procedure only performs a single quick operation with no returned results  setting NOCOUNT ON might yield around a 3  performance boost  2 This would be consistent with a typical insert or update procedure   See the comments on this answer for some discussion about why this may not always be faster   If your stored procedure returns results  i e  you SELECT something   the performance difference will diminish proportionately with the size of the result set

User · Answer

When SET NOCOUNT is ON  the count  indicating the number of rows affected by a Transact-SQL statement  is not returned  When SET NOCOUNT is OFF  the count is returned  It is used with any SELECT  INSERT  UPDATE  DELETE statement  The setting of SET NOCOUNT is set at execute or run time and not at parse time  SET NOCOUNT ON improves stored procedure  SP  performance  Syntax  SET NOCOUNT   ON   OFF     Example of SET NOCOUNT ON     Example of SET NOCOUNT OFF

User · Answer

One place that SET NOCOUNT ON can really help is where you are doing queries in a loop or a cursor  This can add up to a lot of network traffic   CREATE PROCEDURE NoCountOn AS set nocount on     DECLARE  num INT   10000     while  num  gt  0     begin        update MyTable SET SomeColumn SomeColumn        set  num    num - 1     end GO   CREATE PROCEDURE NoCountOff AS set nocount off     DECLARE  num INT   10000     while  num  gt  0     begin        update MyTable SET SomeColumn SomeColumn        set  num    num - 1     end GO   Turning on client statistics in SSMS  a run of EXEC NoCountOn and EXEC NoCountOff shows that there was an extra 390KB traffic on the NoCountOff one     Probably not ideal to be doing queries in a loop or cursor  but we don t live in in ideal world either

User · Answer

Ok now I ve done my research  here is the deal   In TDS protocol  SET NOCOUNT ON only saves 9-bytes per query while the text  SET NOCOUNT ON  itself is a whopping 14 bytes  I used to think that 123 row s  affected was returned from server in plain text in a separate network packet but that s not the case  It s in fact a small structure called DONE IN PROC embedded in the response  It s not a separate network packet so no roundtrips are wasted   I think you can stick to default counting behavior almost always without worrying about the performance  There are some cases though  where calculating the number of rows beforehand would impact the performance  such as a forward-only cursor  In that case NOCOUNT might be a necessity  Other than that  there is absolutely no need to follow  use NOCOUNT wherever possible  motto   Here is a very detailed analysis about insignificance of SET NOCOUNT setting  http   daleburnett com 2014 01 everything-ever-wanted-know-set-nocount

User · Answer

I wanted to verify myself that  SET NOCOUNT ON  does not save a network packet nor a roundtrip  I used a test SQLServer 2017 on another host  I used a VM   create table ttable1  n int   insert into ttable1 values  1   2   3   4   5   6   7  go   create procedure procNoCount as begin   set nocount on   update ttable1 set n 10-n end  create procedure procNormal as begin   update ttable1 set n 10-n end  Then I traced packets on port 1433 with the tool  Wireshark     capture filter  button -   port 1433   exec procNoCount  this is the response packet   0000   00 50 56 c0 00 08 00 0c 29 31 3f 75 08 00 45 00 0010   00 42 d0 ce 40 00 40 06 84 0d c0 a8 32 88 c0 a8 0020   32 01 05 99 fe a5 91 49 e5 9c be fb 85 01 50 18 0030   02 b4 e6 0e 00 00 04 01 00 1a 00 35 01 00 79 00 0040   00 00 00 fe 00 00 e0 00 00 00 00 00 00 00 00 00    exec procNormal   this is the response packet   0000   00 50 56 c0 00 08 00 0c 29 31 3f 75 08 00 45 00 0010   00 4f d0 ea 40 00 40 06 83 e4 c0 a8 32 88 c0 a8 0020   32 01 05 99 fe a5 91 49 e8 b1 be fb 8a 35 50 18 0030   03 02 e6 1b 00 00 04 01 00 27 00 35 01 00 ff 11 0040   00 c5 00 07 00 00 00 00 00 00 00 79 00 00 00 00 0050   fe 00 00 e0 00 00 00 00 00 00 00 00 00   On line 40 I can see  07  which is the number of  row s  affected   It is included in the response packet  No extra packet   It has however 13 extra bytes which could be saved  but probably not more worth it than reducing column names  e g   ManagingDepartment  to  MD    So I see no reason to use it for performance  BUT  As others mentioned it can break ADO NET  and I also stumbled on an issue using python  MSSQL2008 - Pyodbc - Previous SQL was not a query  So probably a good habit still

User · Answer

I guess to some degree it s a DBA vs  developer issue    As a dev mostly  I d say don t use it unless you absolutely positively have to - because using it can break your ADO NET code  as documented by Microsoft     And I guess as a DBA  you d be more on the other side - use it whenever possible unless you really must prevent it s usage   Also  if your devs ever use the  RecordsAffected  being returned by ADO NET s ExecuteNonQuery method call  you re in trouble if everyone uses SET NOCOUNT ON since in this case  ExecuteNonQuery will always return 0   Also see Peter Bromberg s blog post and check out his position   So it really boils down to who gets to set the standards  -    Marc

User · Answer

At the risk of making things more complicated  I encourage a slightly different rule to all those I see above     Always set NOCOUNT ON at the top of a proc  before you do any work in the proc  but also  always SET NOCOUNT OFF again  before returning any recordsets from the stored proc     So  generally keep nocount on  except when you are actually returning a resultset   I don t know any ways that this can break any client code  it means client code never needs to know anything about the proc internals  and it isn t particularly onerous

User · Answer

If you re saying you might have different clients as well  there are problems with classic ADO if SET NOCOUNT is not set ON    One I experience regularly  if a stored procedure executes a number of statements  and thus a number of  xxx rows affected  messages are returned   ADO seems not to handle this and throws the error  Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source    So I generally advocate setting it ON unless there s a really really good reason not to  you may have found the really really good reason which I need to go and read into more

User · Answer

if  set no count   off     then it will keep data of how many records affected so reduce performance   else   it will not track the record of changes  hence improve perfomace

User · Answer

Sometimes even the simplest things can make a difference  One of these simple items that should be part of every stored procedure is SET NOCOUNT ON  This one line of code  put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed  This is performed for all SELECT  INSERT  UPDATE  and DELETE statements  Having this information is handy when you run a T-SQL statement in a query window  but when stored procedures are run there is no need for this information to be passed back to the client   By removing this extra overhead from the network it can greatly improve overall performance for your database and application   If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the   ROWCOUNT option  By issuing a SET NOCOUNT ON this function    ROWCOUNT  still works and can still be used in your stored procedures to identify how many rows were affected by the statement

User · Answer

I don t know how to test SET NOCOUNT ON between client and SQL  so I tested a similar behavior for other SET command  SET TRANSACTION ISOLATION LEVEL READ UNCIMMITTED   I sent a command from my connection changing the default behavior of SQL  READ COMMITTED   and it was changed for the next commands  When I changed the ISOLATION level inside a stored procedure  it didn t change the connection behavior for the next command   Current conclusion    Changing settings inside stored procedure doesn t change the connection default settings  Changing setting by sending commands using the ADOCOnnection changes the default behavior    I think this is relevant to other SET command such like  SET NOCOUNT ON

User · Answer

SET NOCOUNT ON  Above code will stop the message generated by sql server engine to fronted result  window after the DML DDL command execution   Why we do it  As SQL server engine takes some resource to get the status and generate the message  it is considered as overload to the Sql server engine So we set the noncount message on

User · Answer

I know it s pretty old question  but just for update   Best way to use  SET NOCOUNT ON  is to put it up as a first statement in your SP and setting it OFF again just before the last SELECT statement

[sql] SET NOCOUNT ON usage

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to ado.net

Examples related to concurrency