Index design recommendation - how to choose optimal indexes

Indexes should be considered on all columns that are frequently accessed by the WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.

Clustered index recommendations
- It is highly recommended that every table should have at least a clustered index. However, there is also a very interesting topic to be taken into account on
- Consider having a clustered indexes when the data retrieval needs to be sorted.
- Avoid creating a clustered index on columns that are highly updatable as row locator of all the non clustered indexes will be updated accordingly.
- Create the clustered index first. In case the clustered index is created later => all non clustered indexes need to be rebuilt.
- Keep clustered indexes narrow as this directly impacts index size on disk. This is very important as all non clustered indexes store the clustered keys as their row locator, or simpler said:
clustered index row width = non clustered index column width + clustered index column width

Nonclustered Index Recommendations
- A nonclustered index is most useful when all you want to do is retrieve a small number of rows and columns from a large table.
- Consider using INCLUDE clause in order to create covering indexes.
- To improve the performance of a query, SQL Server can use multiple indexes on a table. Therefore, instead of creating wide index keys, consider creating multiple narrow indexes.
- Foreign keys columns are good index candidates.

Disk consideration
- Place the table and index on separate disks.
- Take into account index compression i.e. fewer pages and fewer index levels are needed to store the index.

Column order matters in a composite index
Using the most selective column first will help filter the index rows more efficiently.

Avoid indexes on columns with lower selectivity
If selectivity ratio > 0.85 => a table scan will be preferred.

Take into account column uniqueness
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.

The data type of an index matters
Unless they are absolutely necessary, minimize the use of wide data type columns with large sizes in an index. A large index key size increases the number of index pages, thereby increasing the amount of memory and disk activities required for the index.

Indexed or materialized views
- A database view can be materialized on the disk by creating a unique clustered index on the view. After a unique clustered index is created on the view, the view’s result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing costly operations during query execution.
- Aggregations can be precomputed and persisted in the indexed view to minimize expensive computations during query execution.
- Available only in Enterprise Edition.

Other aspects to be taken into account
- Considering using the Database Engine Tuning Advisor tool provided by SQL Server that helps you determine the correct indexes in a database for a given SQL workload ( a trace file or a table or, new with SQL Server 2012, you can use the queries that exist in the plan cache).
- ColumnStore indexes. Used to index information by columns rather than by rows.
- Spatial indexes.
- XML indexes.
- Index computed columns.
- Consider using filtered indexes.
- Resolve Key or RID lookups by using a clustered index or by using a covering index or by using a index join (i.e. an index intersection between two or more indexes to fully cover a query).

Is this useful?