best practice in SET Options

Question

What are the best practices for the SET options at the SQL Server level ? I understand one's best practice is need not be the same as others. Looking for a generic guideline .

As an example  , I have seen lot of sites , where "no count on " is off "ansi padding " to off , so on and so forth. I would prefer to set the "ser nocount on" to on in all sites .

Any thoughts ? Thanks for your input.

Answer 1

You may do that, but you need to consider that you may break existing applications.. Especially for NOCOUNT I would consider setting it always to NO is a common practice. Otherwise more data is send to the client, which needs to handle it correctly, e.g. Delphi applications using ADO.

Answer 2

Hello DBA_CONSULTING,

Your thread is not related to SQL Server Data Access but to the use of SET. I will move this thread towards the Getting started with SQL Server Forum, where it will interest more people.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 3

Hi DBA CONSULTING,

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

Please see SET Options: http://msdn.microsoft.com/en-us/library/ms190707(v=sql.105).aspx.

Thanks,
Maggie

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 4

i would use the sql server templates as a guide for best practices when using SET functionality

---

John

http://knowledgy.org

Answer 5

@John -

I am looking SET options at the instance level.

Answer 6

@ Maggie -

Thanks . Is there any side effects from setting nocount on from your expereince ?

How about other options such as " Implicit Transactions" , "ANSI ...."

Answer 7

Thanks

Answer 8

Hi Stefan -

How would setting "NOCOUNT on " break exisiting applications ? Please share your experience.

Answer 9

hi, you can checkout the below link, it may help you.

http://jahaines.blogspot.in/2009/06/stored-procedure-set-options-potential.html

Answer 10

The Delphi ADO component sees two result sets when. The default points
to the messages which contains the row count. So when you have a working
procedure with NOCOUNT set to NO, then eveything is fine. When you
change the NOCOUNT to ON, then the application now evaluates the wrong
result set