SQL Server performance with NOCount On

Hi

When working with Database, the most difficult and important thing is to ensure that the database is as fast as possible. After a few year when the number of users in a database grow and the data also start to increase, the performance starts to slide down. At this point it is very difficult to optimize the database for better performance.

This is reason enough that we should keep the performance in our mind from the very early stages of database life cycle. This is not very easy to do because of many factors like changing nature of business, unthought changes in database etc. But there are a few generic tips that can be followed for better performance of the database.

One of the simple tips to be followed is to set the nocount on. This should be a part of all the stored procedure. This one line of code should be there at the top of all the stored procedure. This turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all the following statements SELECT, INSERT, UPDATE, and DELETE.

This information is only handy when we use these statements in a query window like query analyzer. But there is no need of this information to be sent back to the client when we are using the stored procedures from the application. The @@rowcount variable would still work so there we can still pass the data on the number of rows to the client through and output parameter.

Posted via email from fenildesai's posterous

• Tweet
• Share
• Share
• Share
• Share