October 14, 2009 at 4:29 pm
I am asking for some help. I have read countless times in various articles on the advantages of adding the command Set NOCOUNT ON in stored procedures. I have also seen advice on setting this in the default connection options on SQL Server.
Recently, we purchased and installed a Microsoft application that works with SQL Server, but were having issues with one part of the application failing. After several lengthy phone calls with their techs, numerous debug dumps, etc (over the course of a month and a half), we found the problem to be this very issue. NOCOUNT ON was set in Sql Server (in the default connection options), and turning it off eliminated the problem.
Now for my question, the Microsoft tech asked me if I knew whether this is a "Microsoft Best Practice". Is it? Or is it just common sense from the community of SQL users only? If anyone has any best practices from Microsoft directly that state this, please share it. If there is anyone who works for (or did in the past) Microsoft and could shed any light on why they would not recognize this within their application development teams, that would be helpful as well.
In short, I am surprised at this, am I the only one?
October 14, 2009 at 10:09 pm
IIRC, the issue has to do with how ADO (not ADO.Net). ADO uses the rowset count notices as indicators of when the rowset is ready for consumption, as well as how many rows it is going to receive. Consequently, the Server-side must suppress the Counts from intermediate commands, but for best resulsults, must return the Count for the final rowset.
There are also varying issues (I believe) with the older DAO and RDO access methods.
And finally, there is some (slight) performance overhead with it. Normally this is nominal, but if you are using Cursors or WHILE loops (these are ill-advised anyway), it can be substantial.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply