CODEWITHSUNDEEP
performancesql-server-2008t-sql
Timothy Khouri
Timothy Khouri

Reputation: 31875

Can I globally "SET NOCOUNT ON" in TSQL for a query?

There is a stored procedure that we're trying to increase the performance of... at this point we're looking for non-intrusive 'fixes'.

This sproc gets called about 500,000 times a day - and it can call into up to 50 other stored procedures below. - DON'T ASK

Please resist the urge to ask 'OMGz, why!?' questions - but just answer this one if you can :)

Is there a way to SET NOCOUNT ON at the top of one sproc, and have it propagate down into all sprocs and statements below?

EDIT: Judging by the first two answers below - that leaves me to ask... is there a way to set the "user option" in the connection string or something so that it works for one 'user'? - any kind of 'secret tip' like this would be great.

Upvotes: 5

Views: 1979

Answers (3)

Joe Stefanelli
Joe Stefanelli

Reputation: 135888

The scope of SET NOCOUNT ON is to the current object only. There's no way to extend the scope beyond that.

Upvotes: 3

HABO
HABO

Reputation: 15852

See User Options.

Upvotes: 2

JonH
JonH

Reputation: 33183

The short answer is no this is not possible to propogate it. You have to explicitly say:

SET NOCOUNT ON

A global change can be made based on the answer by @user92546 but I am always a bit wary about global changes. The minute you need it you realize that a global change was made...Proceed with caution :).

Upvotes: 4

Related Questions