DataTamer: SQL Server Services

Entries for month: October 2010

Wherefore art thou, nocount?

October 31, 2010 · No Comments

Some days I code faster than I think.  As a practice, I always set nocount to on within a stored procedure to reduce the data being returned from a function call.  I admit that this data is minimal and may not be worth worrying about on smaller systems but I believe in consistency and allowances for growth.

In my development database I wrote a new stored procedure to provide a quick data update on a parent / child type of table set.  This procedure updates child records based on a parent ID as well as a child record seniority.  I tested the procedure by running it and pulling the child records for my given parent ID.  However, I had missed a portion of the where clause.

Do you see the flaw?  I had actually updated the entire child record table but only reviewed the data for a single parent ID.  Setting nocount to on masked this flaw and I didn't catch it until a code review later in the day.

To sum up: using nocount is one of many methods to reduce your data stream, but make sure you turn it off during development and testing.
Categories: