Best practice of creating index for performance tuning.
- Create indexes on the frequently used columns in the WHERE clause and the JOIN criteria of a SQL query.
- Use as small data type as possible when creating indexes.
- Avoid very wide data type columns in an index. Like string data types (CHAR, VARCHAR, NCHAR, and NVARCHAR) sometimes can be quite wide as can binaryand globally unique identifiers (GUID).
- As you can create indexes on a combination of columns in a table but for the best performance, use as few columns in an index as you can.
- Creating an index on columns with a very low range of possible unique values (such as gender) will not benefit performance, because the query optimizer will not be able to use the index to effectively narrow down the rows to be returned.
- Creating an index on multiple columns, which is also referred to as a composite index, column order matters. In some cases, using the most selective column first will help filter the index rows more efficiently.