Do's
• Try to Limit The Result using where clause
• Try to use Default Value for columns
• Use schema before the table
• Use the most efficient (smallest) data types possible. It is unnecessary and sometimes dangerous to provide a huge data type when a smaller one will be more than sufficient to optimize your structure
• Use Union instead of OR, OR will not use benefits of index when you use or like below
SELECT * FROM TABLE WHERE COLUMN1 = 'value1' OR COLUMN2 = 'value1'
On the other hand, using Union such as this will utilize Indexes.
SELECT * FROM TABLE WHERE COLUMN1 = 'value1'
UNION
SELECT * FROM TABLE WHERE COLUMN2 = 'value2'
Don'ts
• Don't Use SELECT *.
• Don't Use the server side cursor.
• Don't Use Sub queries in JOIN. In Sub-query Inner query is executed for each and every row in Outer query. This can be avoided using JOINS.
• Avoid Not in operator in where
• Try to avoid NOT operator in SQL
• Avoid to use functions in where clause.
• Some of us might use COUNT operator to determine whether a particular data exist
SELECT COLUMN FROM TABLE WHERE COUNT(COLUMN) > 0
Similarly, this is very bad query since count will search for all record exist on the table to determine the numeric value of field ‘COLUMN’. The better alternative will be to use the EXIST operator where it will stop once it found the first record. Hence, it exist.
• Try to avoid Wildcard character in your select query
