Category: SQL Server
view more software Tips and Tricks
Updated: 07/29/2016 07:07 AM
Author: Shiju Mathews
As more and more users access the databases, the databases continue to grow, performance slow down. Sometimes even the simplest things can make a difference. One of these simple items that should be part of every stored procedure is SET NOCOUNT ON.
This 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.
By removing this extra overhead from the network it can greatly improve overall performance for your database and application. Please see the template bellow.
References : https://msdn.microsoft.com/en-us/library/ms189837.aspx