Index design guide for SQL server

This article outlines the best practices to follow when creating Indexes for a database.

1. 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.

2. 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.

3.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.

4. 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.

5.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.