Best practices to follow when creating Indexes for a database.
- Examine the WHERE clause and JOIN criteria columns : SQL server Query optimizers looks for indexes on the columns specified in the WHERE clause and the JOIN criteria and if the indexes are available on these columns then optimizer considers Indexes when creating execution plan.
- Use Narrow Indexes : index on a column with narrow data type will improve performance by reducing storage space, reducing required IO and more effective caching.
- Prefer Column with high selectivity : Index on a column with a very low range of unique value will not yield any benefit in performance as query optimizer will not be able to narrow down the rows to be returned. So it is always good to create index on columns with high selectivity.
- Consider the Column Data Type : Data type of an indexed column is an important consideration. Index search on integer keys is very fast because of small size and easy arithmetic operation of the integer data type, however string data type require string match operation which is costlier than integer operations.
- Column Order : Column order in the index must be the same as the column order in the WHERE cluase of your query, otherwise optimizer will not be able to use index as index keys will be sorted differently.