SQL SERVER – Best Practices for Query Optimization

--> Table should have primary key
--> Table should have minimum of one clustered index
--> Table should have appropriate amount of non-clustered index
--> Non-clustered index should be created on columns of table based on query which is running
--> Following priority order should be followed when any index is created
a) WHERE clause
b) JOIN clause
c) ORDER BY clause
d) SELECT clause
--> Do not to use Views or replace views with original source table
--> Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
--> Remove any adhoc queries and use Stored Procedure instead
--> Check if there is atleast 30% HHD is empty – it improves the performance a bit
--> If possible move the logic of UDF to SP as well
--> Remove * from SELECT and use columns which are only necessary in code
--> Remove any unnecessary joins from table
--> If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)