Is there any reason not to use "SET NOCOUNT ON" in a stored procedure?
The seemingly obvious possibility that it must be OFF to use @.@.ROWCOUNT is countered by the BOL entry which says "The @.@.ROWCOUNT function is updated even when SET NOCOUNT is ON."
That entry also states "For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced".
I have always understood that is good practise to have SET NOCOUNT ON is a stored procedure.
So is there any advantage (other than one less line to enter) of not having SET NOCOUNT ON is any stored procedure?
I do not think that the status of the NOCOUNT setting has anything to do with the use of the @.@.rowcount internal; look at this:
Code Snippet
set nocount on
select 1 as [on] union all select 2
select @.@.rowcount as [@.@.rowcount]
set nocount off
select 1 as [off] union all select 2
select @.@.rowcount as [@.@.rowcount]
/*
on
--
1
2
@.@.rowcount
--
2
off
--
1
2
(2 row(s) affected)
@.@.rowcount
--
2
(1 row(s) affected)
*/
I think that we had some old apps that had trouble when multiple ROWCOUNT messages were returned, but I don't really know the details of that. And this trouble had a good deal to do with setting NOCOUNT to ON in those particular applications.
This is just one of those examples of why you should be very specific when you name things.
SET NOCOUNT ON *sounds* like it would suppress the rowcount being taken/stored, but in essense all it does is suppress the message that is sent to the client, so SET ROWCOUNT_MESSAGE OFF would be the best way to name it (or at the very least, the best way to think of it.)
The only advantage of not having it on is if your tools use the message for some purpose. Like if the UI expects to see the message after operations, and actually uses the information, then it is interesting.
|||Set NOCOUNT ON is used to suppress the feedback message from the Server to Client. It wont affect the @.@.ROWCOUNT global variable.
It is something similar to the oracle's SET FEEDBACK OFF
|||
> So is there any advantage (other than one less line to enter) of not having SET NOCOUNT ON is any stored procedure?
Depend what are you using this sp for. Read the first note.
Updating Data Sources with DataAdapters
http://msdn2.microsoft.com/en-us/library/33y2221y(VS.80).aspx
AMB
|||>Updating Data Sources with DataAdapters http://msdn2.microsoft.com/en-us/library/33y2221y(VS.80).aspx
Thank you for the refererence! Now a positive reason not to use "SET NOCOUNT ON", it interferes with the edit and update action on a DataAdapter, by causing an exception.
Any other reasons please?
