what is this "SET NOCOUNT ON "

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

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

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

@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

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

"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

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

Nice Explanation

Thanks & Regards
Hari

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