Some of the Performance tuning tips (good for Developers also).
- Substring function: SARG cannot be produced and an index cannot be used (ignores cluster index scan)
- Queries: what is good today may not be good tomorrow.
- Operate a small result sets, limit that number of column on select list
- Use highly selective where clause.
- Using index effectively as we did some query on the feature already.
- Important is to avoid non index able search condition
- Index able where clause: >, <, >=, between, %
- Non index able where clause: !=, !>,!<, not exist, not in , not like, or, like, %%
- Avoid arithmetic operator
- Substring vs. like (like would be good)
- Avoid optimizer hints
- Avoid implicit conversation.
- Union vs. union all (union all would be good)
- Sub query vs. joins (sub query would be good)
- Store procedure should have: set nocount on… set nocount off
- Don’t use function in where clause