How to make the SQL Query Optimized.

Few Tips to make the SQL Query Optimized

Following are few tips to make your SQL Queries Optimized to reduce CPU usage to process it.  

• The Table should have a primary key. 
• Always use WHERE Clause in SELECT Queries while we don’t need all the rows to be returned.
• Table should have minimum of one clustered index.
• Remove * from SELECT statement and use column Names instead which are only necessary in code.
• Always avoid the use of SUBSTRING function in the query.
• Remove any unnecessary joins from table.
• While there is case to use IN or BETWEEN clause in the query, it is always advisable to use BETWEEN for better result.
• Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause.
• Triggers should not be used if possible, incorporate the logic of trigger in stored procedure.
• Check if there is at least 30% Hard Disc is empty – it improves the performance a bit
• Remove any adhoc queries and use Stored Procedure instead.
• When we are writing queries containing NOT IN, then this is going to offer poor performance as the optimizer need to use nested table scan to perform this activity. This can be avoided by using EXISTS or NOT EXISTS.
• While the select statement contains a HAVING clause, its better to make the WHERE clause to do most of the works (removing the undesired rows) for the Query instead of letting the HAVING clause to do the works.
• Do not to use Views or replace views with original source table.