How to improve the performance of stored procedure

To improve the performance of stored procedure we have to keep few things in mind.

1- Always  use SET NOCOUNT ON it can improve performance because network traffic can be reduced because the message that indicates the number of rows that are affected by the T-SQL statement is not returned as part of the results. When you use SET NOCOUNT OFF; the count is returned.

2- Avoid to use wild card(*) we should avoid to SELECT * or all the column of table with select statement. we can use the column name which is required.

Example: Select * from employee  (need to avoid) we can use like Select column name from  employee.

3-Use IF EXISTS or SELECT 1 to check the value or record in table.

4- USE with (nolock) keyword while selection data from table.

example: SELECT * FROM Employees WITH(NOLOCK)

5- Avoid to use cursor if possible.

6-Use indexing on table column.

7-Use fully qualified procedure name like  database.schema.objectname

8-Avoid to set the procedure name prefix with sp_ keyword.