Optimization of Stored Procedure

• With every Select statement & DML statements the SQL returns the message that indicates no of rows effected  by that statement. This information is mostly helpful when we are debugging the code. Next it is not useful. This Feature can be disable that.

• Use Schema name infront of table name. i.e dbo.Tablename

• Do not use SP_ infront of stored procedure. Because SQL Server searches in the Master db. Thenit causes extra over head.

• Try to avoid using SQL Cursors inside the Stored Procedure.

• Keep the transactions as short as possible. The length of the transaction effects blocking and dead locking. Exclusive lock is not released until the end of the transaction.

• Apply proper index in the table column.

• Do not use SQL * into the SQL Query

• Avoid unnecessary columns that are in the SELECT Statement.

• Try to avoid joining between two different types of columns.

• Try to avoid deadlocks

• Try to avoid Temporary tables.

• Instead of LIKE search use full text Search for searches textual data.

• Implement lazy loading strategy for large tables. Storage of large object columns(varchar(max), image(text) in a different tables then the main table and put the reference to the large objects in the main table.

• Do not call support functions repeatedly with in your stored procedure, triggers, functions & batches.

• If you have a large stored procedure, try to break down   this stored procedure into several sub procedures and calling them for c controlling stored procedure.