Where is the best place for SET NOCOUNT?

Question

For a large database (thousands of stored procedures) running on a dedicated SQL Server, it’s best to include it SET NOCOUNT ONat the top of each stored procedure or set this parameter at the server level (Properties → Connections → "no count"). It appears that the DRY (Do Not Repeat Yourself) principle applies, and this parameter should only be set in one place. If SQL Server also hosted other databases, this might object to installing it at the server level, as other applications may depend on it. Where is the best place for SET NOCOUNT?

Answer 1

Make this the default for the server (which would be for historical reasons). I have been doing this for all servers from the very beginning. Have you ever wondered why it sets NOCOUNT ON instead of SET COUNT OFF? This is because the only UI back in Sybase days was the CLI; and it was natural to show the score when the query could not show any results, and therefore there was no indication of this.

Answer 2

Since this is a dedicated server, I would set it at the server level so as not to add it to each stored procedure. The only problem would be if you wanted to save a stored procedure that did not have a counter.