Building SQL Queries - Key points and facts

Below points are DBMS/RDBMS independent and can be applied to any database.
Always try to keep your SQL code simple since tuning a simple code is easier than a complex code and for the database optimizer which chooses the execution plan will function well with less complex code

1. It is always faster to select exact columns than putting * in the select statement
2. Selecting columns which are in clustered or non-clustered index have better performance since the possibility of optimizer using the index is more.
3. Avoid using NOT in the queries since query optimizer ignores indexes and does a table scan.
4. Always try to match comparison condition column sequence with existing index column sequences.
5. Always try to use unique, single –column indexes wherever possible.
6. Avoid using the primary index column in the order by clause when in the select statement primary index column is there.
7. ORDER BY clauses does not use indexes rather they scan the complete table or dataset
8. HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.

9. Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
10. Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship
11. Try to use UNION ALL instead of UNION if the two datasets are unique.
12. To write queries which provide efficient performance follow the general SQL standard rules.
a. Use single case for all SQL verbs
b. Begin all SQL verbs on a new line
c. Separate all words with a single space
d. Right or left aligning verbs within the initial SQL verb
13. Avoid using FUNCTIONS where an SQL index can be used like WHERE clause.
14. Using an equals sign (equi) is the fastest comparison condition if a unique indexes.
15. Always try use LIKE operation without ‘%’ otherwise it will lead to table scanning rather than reading from an index.
16. If the database table has VARCHAR field and should be compared better to go for FULL TEXT INDEXING feature of SQL SERVER.
17. A Join is efficient if when it can use indexes on large tables, or is reading only very small table.
18. INNER JOIN is efficient when index columns are matched together in JOIN clauses. Indexes are only needed if the table is huge. If the table contains small data optimizer might choose table scan rather than reading index.
19. Basic steps to tune JOINS
a. Use equality first.
b. Use range operators where equality does not apply.
c. Avoid use of negatives in the form of != or NOT.
d. Avoid LIKE pattern matching.
20. Use indexes wherever possible except for small tables and join tables on indexed columns.
Check for the Execution plan (SQL Server) if the queries are using proper indexes or doing any table’s scan where they shouldn’t be.