Index design guide for SQL server

This article outlines the best practices to follow when creating Indexes for a database. such as using Narrow data types columns, using column with high selectivity, careful consideration for column order in index.

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.


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: