what is this "SET NOCOUNT ON "

Posted by Gow.Net under Sql Server on 8/17/2012 | Points: 10 | Views : 1553 | Status : [Member] | Replies : 8
what is this "SET NOCOUNT ON " in stored procedure what is use of this?

gowthaman8870226416


Responses

Posted by: Ranjeet_8 on: 8/17/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
SET NOCOUNT is ON, the count is not returned and when SET NOCOUNT is OFF, the count is returned.
for more info plz refer this url :
http://www.dotnetfunda.com/articles/article581-set-nocount-transact-sql.aspx

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Laghaterohan on: 8/17/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

SET NOCOUNT ON is a keyword of SQL server when used turns off / does not return the number of rows affected by the each T-SQL statement messages to the client.

When used in the stored procedure it greatly improves the performance, since putting this keyword does not return the number of rows affected messages to the client as it is of no use in this case.
Having thing information is useful when you have a normal query and not a stored procedure.

I hope this helps you.

Thanks.







Best Regards,
Rohan Laghate

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kirthiga on: 8/20/2012 [Member] Starter | Points: 25

Up
0
Down
SET NOCOUNT ON in stored procedure used to stop the message that shows the number of rows affected by the SQL statement.

Stored Procedure contains several select, alter and update statements. The result pan shows the number of rows affected by the SQL statement written in the stored procedure. By using SET NOCOUNT ON network traffic can be greatly reduced. The overall performance of the database must gradually increase.



Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jasminej on: 8/20/2012 [Member] Starter | Points: 25

Up
0
Down
@Kirthiga

If so (NOCOUNT ON reduce the network traffic), How many bytes/KBs can be reduced ? for simple select statement which process 10 record(s) ?

thanks in advance

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kirthiga on: 8/21/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Jasmine

I am not sure how you will get it in bytes.
But to know the performance eficiency of a query you can use sql server profiler.
For a single select statement network traffic will not have that much change.
By using query execution plan we can estimate cost for select statements.



Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/18/2012 [Member] Bronze | Points: 25

Up
0
Down
"SET NOCOUNT ON " line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. Having this information is handy when you run a T-SQL statement in a query window, but when stored procedures are run there is no need for this information to be passed back to the client.

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/18/2012 [Member] Bronze | Points: 25

Up
0
Down
Mark as answer if it helpful to you..That helps others who search the same...

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hariinakoti on: 9/18/2012 [Member] Starter | Points: 25

Up
0
Down
Nice Explanation

Thanks & Regards
Hari

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response