Basic SQL Code Review

PROBLEM:

You want to make SQL code review to yourself or to one of your team member

SOLUTION:

Use the following list for a basic SQL code review. Remember - recommendations are good for 90% of cases. Use "Execution plan" to validate your queries

1. Always use indexes, even for small tables
2. Try to avoid "OR"s - may cause full table scans (UNION can replace OR)
3. Use UNION ALL instead of UNION if possible (does not sort)
4. Do not use SQL functions around indexed columns (will suppress the use of that index)
5. Try to avoid sorts (or use the same order by as the index)
6. Try to provide the WHERE clause with as many host variables that match the index
7. Avoid JOIN's with many tables
8. Use an OUTER JOIN instead of the NOT IN clause
9. Using col1 > 0 is better that col1 != 0
10. Use col1 = 40 not col1+10 = 50
11. Use EXISTS instead of DISTINCT and IN subquery
12. Use NOT EXISTS instead of NOT IN
13. Use UNION instead of OR
14. When 2 indexes exists on the same table and both rank the same (by the optimizer), the first index on the WHERE clause will be used
15. Try to use "index only" queries if possible. Consider adding columns to existing indexes
16. OR's are processed from left to right
17. AND's are processed from right to left
18. Put expensive OR's at the end part (right)
19. Put expensive AND's at beginning part (left)
20. Make proper use of correlated sub-queries
21. Break up complicated SQL statements. Do not attempt to solve all situations with single statement
22. Beware of NULLS! - IS NULL and IS NOT NULL will not use index
23. Use >= 0 to replace NULL expression 

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!