The indexes are structures in the databases created to tune the database performance. They are used to speed up data retrieval.
The types of indexes are:
- Clustered index
- Physical ordering of data in a table based on a specific column or set of columns.
- A table can only have one clustered index
- Non-Clustered index
- Separate structure that contains pointers to data rows in a table, allowing for efficient data retrieval based on specific columns without affecting the physical order of the data.
- A table can have many Non-Clustered indexes
The main difference between clustered and non-clustered indexes lies in their impact on the physical storage of data. A clustered index determines the physical order of the data in the table, while a non-clustered index is a separate structure that points to the data rows, allowing for efficient retrieval without affecting the physical order.
Other Index Types
This is not a comprehensive list
- Unique index
- Ensures that all values in the indexed column(s) are unique
- Composite Index
- Created on multiple columns, allowing for efficient retrieval based on combinations of column values
- Primary Key Index
- A special type of unique index that uniquely identifies each row in a table
- Foreign Key Index
- Enforces referential integrity by pointing to a primary key in another table.