How to optimize a stored procedure?

Below are few steps which can be followed in the procedure to optimize the query:

1. To count number of rows use “select count(1) from the table_name” or “select count(Primary_key) from the table_name” avoid using “select count(*) from the table_name”

2. Always select limited number of column for which data is required avoid “select * from the table_name”

3. Always use filter where, having clauses to restrict the data as much/early as you can.

4. Join tables which are required in the query avoid unnecessary joins in tables

5. Use Exists and Not exists for sub- queries, it will get the data faster

6. Use of distinct / minus keyword should be minimized

7. Use table variables for short term and small data store while executing a procedure

8. Avoid using mix data types as this will increase conversion overhead. Use the same data types which are there in the table. Avoid using cast/ convert functions as these make slower execution of procedure

9. While declaring a variable uses the appropriate length of variable which is required, it can waste the memory/ truncate the string at the end.

10. Avoid using Cursors in SQL as the SQL Server optimizer works in a better way while executing set based dataset.