Increasing performance by creating a non-clustered index

It is now clear that indexes improve performance for most of the SELECT statements, if it is created wisely on a proper key field. There is one limitation on clustered indexes—only one clustered index is allowed per table, and in many cases, it may not be possible to cover all the required columns in one clustered index. There is another object provided by SQL Server, known as non-clustered index, which could be used on one or more than one column.

If you cover one highly selective column in the clustered index, it is not certain that you are going to use that column only, in all the WHERE and JOIN statements, especially when a table has many columns. In this scenario, we have to create a non-clustered index on the selective fields that are not covered inside a clustered index, so that we can get a performance benefit while using those fields as a predicate in the SELECT query.

Note

Till SQL Server 2005, 249 non-clustered indexes were...