An index is used to speed up the performance of queries. It does this by reducing the number of database data pages that have to be visited/scanned.
- An index can be used to efficiently find all row matching some column in your query and then walk through only that subset of the table to find exact matches.
- If you don't have indexes on any column in the 'where' clause, the SQL server have to walk through the whole table and check every row to see if it matches, which may be a slow operation on big tables.
Types of Indexes:
- a clustered index determines the physical order of data in a table. There can be only one clustered index per table.
- If you create a primary key on the table you create clustered index on this table. The primary key is always clustered index.
- All other indexes on a table are termed non-clustered.
- A non-clustered index is also a binary tree but it doesn't create a physical order of rows. So the leaf nodes of non-clustered index contain PK (if it exists) or row index.
A table without a clustered index is called a "heap" because it's a pile of unstructured data