SET NOCOUNT ON;
-
As we know that "Set NoCount ON;" increases the performance in SQL server queries by avoiding any extramessages being sent to client... We are using this statement invarialbly in all procedures and I have seen it being part of SQL Server default procedure template... What I couldnt figure out is if we need to use this with all the statements why is it OFF by default??? What are the cases where we shouldn't be using it???
~AHAGeek
-
As we know that "Set NoCount ON;" increases the performance in SQL server queries by avoiding any extramessages being sent to client... We are using this statement invarialbly in all procedures and I have seen it being part of SQL Server default procedure template... What I couldnt figure out is if we need to use this with all the statements why is it OFF by default??? What are the cases where we shouldn't be using it???
~AHAGeek
-
A _sp that returns a result set and you want the count (often for queries and reporting purposes).
Any suggestions, ideas, or 'constructive criticism' are always welcome.
In SP if we want count..we can still use @@RowCount with this statement on???
~AHAGeek
-
In SP if we want count..we can still use @@RowCount with this statement on???
~AHAGeek
Typically I would use @@ROWCOUNT in a stored procedure where there are several transactions that are going to modify records. You can then be certain that every update, delete, insert occurs as expected; esp. in stored procedures where it's all or nothing. If 1 table doesn't get modified as expected, you can then rollback. IE: You have a SP (with NOCOUNT = ON) that has several table updates for updating general ledger entries for A/R and then write the changes into a history table. If one update fails (by checking @@ROWCOUNT), the entire transaction gets rolled. Returning a rowcount (meaning NOCOUNT = OFF) is good for a very simple sps that return a result set and the row count can be displayed for information purposes and ensure the query didn't error off.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
-
As we know that "Set NoCount ON;" increases the performance in SQL server queries by avoiding any extramessages being sent to client... We are using this statement invarialbly in all procedures and I have seen it being part of SQL Server default procedure template... What I couldnt figure out is if we need to use this with all the statements why is it OFF by default??? What are the cases where we shouldn't be using it???
~AHAGeek
I suggest the default is bc it is useful when coding a proc, it is only when you go to production that you want to turn it off. It would be nice to be able to turn it off as a database/server option.
Never underestimate the power of human stupidity RAH