SET NOCOUNT ON usage

by

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/

More Related Contents:

  1. Only inserting a row if it’s not already there
  2. Calculate a Running Total in SQL Server
  3. Conditional WHERE clause in SQL Server
  4. How to Join to first row
  5. How can I convert bigint (UNIX timestamp) to datetime in SQL Server?
  6. Concatenate row values T-SQL
  7. Multi-statement Table Valued Function vs Inline Table Valued Function
  8. How do you return the column names of a table?
  9. How to return multiple values in one column (T-SQL)?
  10. Dynamically create columns sql
  11. Reference alias (calculated in SELECT) in WHERE clause
  12. When to use Common Table Expression (CTE)
  13. INT vs Unique-Identifier for ID field in database
  14. How to send email from SQL Server?
  15. How To Create Table with Identity Column
  16. Why do you create a View in a database?
  17. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  18. How to split string using delimiter char using T-SQL?
  19. Calculate the number of records for each date between 2 dates
  20. T-SQL: How to use parameters in dynamic SQL?
  21. How to compare datetime with only date in SQL Server
  22. Merge overlapping date intervals
  23. SQL server select distinct rows using most recent value only
  24. Access to Result sets from within Stored procedures Transact-SQL SQL Server
  25. Split words with a capital letter in sql
  26. SQL poor stored procedure execution plan performance – parameter sniffing
  27. Eliminate and reduce overlapping date ranges
  28. scope_identity vs ident_current
  29. COALESCE Function in TSQL
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment