Archive for September 2012

NOCOUNT Command in sql server

NOCOUNT command returns number of rows affected by T-SQL statements after the command execution.

Syntax for NOCOUNT is :

SET NOCOUNT {ON/OFF}

SET NOCOUNT OFF :  By default NOCOUNT option will be set to OFF in sql server. When ever a T-SQL statement is executed in sql server, then it will return the count of number of rows affected after the statement completion.

SET NOCOUNT ON :  This command will not return the count of the number of records affected after command execution.

This option will reduce the extra overhead to the network , which will improve the performance of the sql code.

SET NOCOUNT ON option is especially useful in case of stored procedures.

If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option.  By issuing a SET NOCOUNT ON this function @@ROWCOUNT still works and can still be used in stored procedures to fetch information about how many rows were affected by the statement.


Posted in | Leave a comment

What are the uses of SET NOCOUNT ON in sql server?

By deafault NOCOUNT will be set to OFF in sql server. This will return number of  rows affected at the end of each T-SQL statement exceution. In a stored procedure which comprises of many T-SQL statements , its not wise to return the number of rows at the end of each statement execution.

For printing the message with number of rows affected each time, sql server uses some part of system resources; Which is unneccesarily wasted for displaying unuseful informatio.
SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.

For a stored procedures containing many T-SQL statements that do not return much actual data, SET NO COUNT ON can provide a significant performance boost because network traffic is greatly reduced.

Posted in | Leave a comment

SQL Server Performance tuning tips : SET NOCOUNT ON at the top of stored procedure

TIP: Always SET NOCOUNT ON at the top of stored procedure for better performance.


By deafault NOCOUNT will be set to OFF in sql server. This will return number of  rows affected at the end of each T-SQL statement exceution. In a stored procedure which comprises of many T-SQL statements , its not wise to return the number of rows at the end of each statement execution.

For printing the message with number of rows affected each time, sql server uses some part of system resources; Which is unneccesarily wasted for displaying unuseful informatio.
SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.

For a stored procedures containing many T-SQL statements that do not return much actual data, SET NO COUNT ON can provide a significant performance boost because network traffic is greatly reduced.

Posted in | Leave a comment

What are the disadvantages of TRUNCATE in SQL Server?

TRUNCATE command consumes less time compared to DELETE. Also, Truncate operation will not consume space in transaction log.

Despite of these advantages, TRUNCATE will have its own disadvantages.

  • TRUNCATE will reset the seed of an identity column to its initial value. If we don't want seed of identity column to reset after data deletion from table, then truncate is not an apt choice.

  • Statistics on table will not be updated automatically after TRUNCATE operation unless AUTO UPDATE STATISTICS is ON . So, After every TRUNCATE Statement it's required to update the statistics on table manually.  If this operation is not done, then wrong statistics will up used for sometime, which may hit the performance.If you wait long enough, and if you have Auto Update Statistics turned on for the database, then the statistics will eventually catch up with themselves. But this may not happen quickly, resulting in slowly performing queries because the Query Optimiser is using out-of-date statistics.  

Posted in | Leave a comment

SQL Server Performance tuning Tips -- Use TRUNCATE Instead of DELETE

Tip: AVOID DELETE operation if the same task can be performed using TRUNCATE.

If you want to delete complete data from a table, then go for TRUNCATE instead of DELETE.

DELETE operation will maintain transaction log for each and every record it deletes from the table. This will unnecessarily consumes space in transaction log.

Where as TRUNCATE operation will not maintain the log and it will delete all the records from table at one shot.

Also, DELETE operation will consume significantly huge amount of time if the table is of big size.

In order to have better performance and to run the query with in shorter time use TRUNCATE instead of DELETE.

Posted in | Leave a comment

Sql server performance tuning tips -- Don't Run Screen Saver

TIP: Don't Run Screen Saver

Running Screen saver in a machine consumes significant amount of CPU time.
If we are running the screen saver in the same machine as that of SQL Server running, then some part of CPU time is unnecessarily wasted for running screen saver.

So, When ever a query is running in sql server, always go for a blank screen saver instead of flashy and animated screen savers.

Posted in | Leave a comment