SET NO COUNT OFF in sql stored procedures

SET NO COUNT OFF:



In most of the SQL server procedures, we used to see this command. In the top of the procedure, we will see SET NO COUNT OFF and in the end, they wll turn on the same. FUNNY RIGHT..... WHY THEY ARE DOING THIS FOR EACH AND EVERY PROCEDURE. GO AND DO IT IN THE DATABASE OR SERVER LEVEL....WHY WE ARE NOT DOING IT? LETS SEE.



CREATE PROCEDURE VENKATPROC
AS
BEGIN
SET NOCOUNT OFF
--- YOUR SQL STATEMENTS.
SET NOCOUNT ON
END




Funn
Yes, its correct. From performance point of view. Set no count off is essential for the stored procedures and functions.

Considering, you have an insert statement which inserts 3 records. SQL server will insert three records and it will update to the client as 3 records got updated. In some cases, this data is not needed for the client. Unnecessarily, this data will be pumped out from the server to client.
This can be avoided by put set no count off in the start of the procedure and it will be removed at the end of the procedure.

Thanks and Regards,
Venkatesan Prabu .J