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.

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.

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.