1. Remove * from SELECT and use columns which are only necessary in code
2. Remove any unnecessary joins from table
3. Always use WHERE Clause in SELECT Queries while we don’t need all the rows to be returned.
4. Avoid SELECT * FROM OrderTable WHERE LOWER(UserName)='telsa' , because case insensitive.
5. The operators shown below are in their decreasing order of their performance.
ie, better to go for '=' rather than 'LIKE' or '<>'
6. NOT IN, then this is going to offer poor performance, this can be avoided by using EXISTS or NOT EXISTS.
7. When there is a choice to use IN or EXIST, we should go with EXIST clause for better performance.
8. SELECT * FROM OrderTable WHERE Status = 1 AND OrderID IN (222,444,111,555)
Takes more time than
SELECT * FROM OrderTable with (INDEX=IX_OrderID) WHERE Status = 1 AND OrderID IN ( 222,444,111,555 6)
9. While there is case to use IN or BETWEEN clauses in the query, it is always advisable to use BETWEEN for better result.
10. Always avoid the use of SUBSTRING function in the query.
11. Provide the least likely true expressions first in the AND. Because if the first condition is false the result also false, ie it never want to check the second condition of AND operation .
12. It is sometimes better to combine queries using UNION ALL instead of using many OR clauses.
13. SELECT * FROM CustomerTable WHERE City = 'Wichita' OPTION(FAST n). In some scenario's we have to display only 10-15 result set in the web page . But in database it having more than Ten Thousand row. In this time the data base will give the 'n' result set very fast, and rest of the data will update soon.
14. Avoid Expensive Operators Such as NOT LIKE
15. WITH NOLOCK and WITH READUNCOMMITTED
16. Avoid Long-Running Transactions. it has to do by the sys admin.
17. Use Set NOCOUNT ON in stored procedures.
18. You can rewrite a subquery to use JOIN and achieve better performance.
19. Performance increase by adding a unique key for table. (it can index the table by unique key)