SQL Server Set NOCOUNT on/off

Question

To achieve better performance we use Set NOCOUNT on inside the SP?

Is it mandatory to mention Set NOCOUNT off at the end of the SP?

If not will it harm anywhere?

Thanks

Answer 1

set nocount on will disable the X rows affected. message SQL returns. This message is suppressed, in some cases, due to undesired effects with the client executing the stored proc.

set nocount off will undo this suppression. However, set nocount on is a scope setting, and by default, will be turned off when leaving the scope anyway.

Now, is set nocount off necessary? No, as any new commands executed will be in a different scope, and by default set nocount off is always in effect. But as stated above in comments, it's considered a good practice, just to explicitly indicate that this setting will return to normal when the proc is finished executing.

Is it mandatory to mention Set NOCOUNT off at the end of the SP? - No

If not will it harm anywhere? - No