Indexes are one of the most powerful objects in the RDBMS system, though the index itself is not a relational concept. It significantly reduces disk I/O and logical reads, to boost up the performance of the SELECT statement by locating proper data without even scanning the whole table. That is why it is mandatory to have a proper index on proper column(s) of the table. Missing indexes or Indexes on improper column(s) could start creating performance-related issues, such as implanting a wrong execution plan, which may create high I/O use and logical reads. Indexes are a double-edged sword, so use them with caution, otherwise it may be harmful for performance. This is because indexes come with a little overhead for DML statements, which requires storage space on the disk, and keeping your index up to date with changing data is also one of the overheads.
We could like to compare indexes to an English dictionary (this is really a widely used analogy for indexes). Suppose we are...