Why sometimes when query is not wor

[ClaireHCM]

Why sometimes when query is not working or working poorly?we will be suggested or reminded "did you set nocount on ?"Why set nocount on affect the result of our query?how does it affect the result(or should I say performance)?

 

[JamesLean]

SET NOCOUNT ON just suppresses the informational messages that tell you how many rows were affected by the last statement, eg:

(4094 row(s) affected)

They shouldn't affect the performance of a query much (if at all). The main time it is suggested you use it is at the top of your stored procedures, especially when they will be called through ADO. The informational message gets returned as a seperate recordset and can cause problems when trying to view the actual query results.

--James

 

[ClaireHCM]

so set nocount on is sort of saving system resources?
What kind of problem it usually creates if I dont set it on?
Under what situations ,set nocount on is better to be coded?
Does it always good to set nocount on?