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
JOIN statements, especially when a table has many columns. In this scenario, we have to create ...