SQL Optimization Techniques

1. Include Schema name along with the table name.
 
2. Make transaction as less as possible.

3. The DB cache should be ON.

4. While Using UNION try using UNION ALL statement wherever necessary as it works faster.
UNION ALL will not discard duplicate rows whereas UNION looks for Duplicate rows. Thus UNION ALL works faster.

5. Add SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
It will reduce the network traffic.

6. Use Clustered Indexes, it will help in achieving index seek instead of a index scan.

7. Remove user-defined inline scalar functions as it works on row by row basis.

8. Using the DISTINCT clause will result in some performance degradation, you should use this clause only when it's necessary.

9. Use stored procedure instead of text queries. As stored procedure gets precompliled after its first execution.

10. Try using constraints as an alternative to triggers, whenever possible.
example : suppose if we are using instead of trigger while inserting data in table and raising error whenever the value of a column is greater than 100.
Here  instead of using a trigger we should use constraints.

11. Avoid cursors as it increases the execution time.

12. Less use of sub queries.

13. Try using CTE common table expression instead of using temp tables so as to avoid network overhead created while using temp variables.

14. Do not create Stored procedure name starting with prefix "sp_". As the master database consists of stored procedures names starting with "sp_" and
if your sp name and the sp in the master database is same your stored procedure will never get executed.
Other disadvantage is when a sp name with sp_ prefix is getting called then it will be first searched in the master database then into the actual Database.Thus taking more time for execution.

15. Using of sp_executesql can help where just the parameter values is getting changed , as the same execution plan is used again and again.