Indexes are the single most important factor in database performance. Without them, SQL Server must read the entire table (Table Scan) every time you want to find one row. There are two primary types of indexes: Clustered and Non-Clustered.
A table can have only ONE clustered index because the data rows themselves are physically sorted and stored according to the index key. Think of it like a Dictionary where the words are physically sorted alphabetically. If you have a clustered index on Email, the rows are literally moved on the disk to stay in alphabetical order.
A table can have hundreds of non-clustered indexes. It is a separate object that contains a sorted list of your key columns and a Pointer (RID or Clustered Key) back to the actual data row. Think of it like the Index at the back of a textbook—you find the word, look at the page number, and then flip to that page.
When you use a non-clustered index to find a row, SQL Server must then perform a Key Look-up to fetch the other columns. If your query returns 1 million rows, these 1 million points back to the main table can be slower than a simple table scan!
Q: "Should I always create a Clustered Index on a Primary Key (ID)?"
Architect Answer: "Usually, yes. An auto-incrementing ID is perfect because new rows are always added to the end of the table, preventing 'Page Splits'. However, if 99% of your queries search by `CreatedDate`, it might be more efficient to make `CreatedDate` the clustered index so that your daily reports read data sequentially from the disk instead of jumping around."