This is a setting that DBA are advised to place at the start of the stored procedure.What this setting does?Let us run an sample code for this.USE AdventureWorks
GO
SELECT * FROM Person.Address
SET NOCOUNT ON
SELECT * FROM Person.Address
SET NOCOUNT OFFYou will that first select query (at line no 3), will execute with out the the above setting while second SELECT statement (at line no 7) will execute with the SET NOCOUNT ON setting. Though both queries will result same output. So what is the different.If you got the message tab, you will see following output there.Only first query has returned the number of rows effected not the second query. This is what BOL says,“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.” In simple term, SET NOCOUNT ON will not return number of records affected to the client. What is the bug deal. If you are not using this information, it is an unnecessary burden to your server and network. By not returning this information, you are using less resources. This might not be huge saving, but might be the string which will brake the back of the camel. Read more: Dinesh Asanka
GO
SELECT * FROM Person.Address
SET NOCOUNT ON
SELECT * FROM Person.Address
SET NOCOUNT OFFYou will that first select query (at line no 3), will execute with out the the above setting while second SELECT statement (at line no 7) will execute with the SET NOCOUNT ON setting. Though both queries will result same output. So what is the different.If you got the message tab, you will see following output there.Only first query has returned the number of rows effected not the second query. This is what BOL says,“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.” In simple term, SET NOCOUNT ON will not return number of records affected to the client. What is the bug deal. If you are not using this information, it is an unnecessary burden to your server and network. By not returning this information, you are using less resources. This might not be huge saving, but might be the string which will brake the back of the camel. Read more: Dinesh Asanka
Posts
0 comments:
Post a Comment