Database - Explain the types of indexes

Explain the types of indexes.

1) Clustered index
2) Non-clustered

Clustered index

- Clustered index exists as sorted row on disk.
- Clustered index re-orders the table record.
- Clustered index contains record in the leaf level of the B-tree.
- There can be only one Clustered index possible in a table.

Non-clustered

- Non-clustered index is the index in which logical order doesn’t match with physical order of stored data on disk.
- Non-clustered index contains index key to the table records in the leaf level.
- There can be one or more Non-clustered indexes in a table

Types of indexes.

The types of indexes are:

1. Clustered: Clustered index sorts and stores the rows data of a table / view based on the order of clustered index key. Clustered index key is implemented in B-tree index structure.

2. Nonclustered: A non clustered index is created using clustered index. Each index row in the non clustered index has non clustered key value and a row locator. Locator positions to the data row in the clustered index that has key value.

3. Unique: Unique index ensures the availability of only non-duplicate values and therefore, every row is unique.

4. Full-text: It supports is efficient in searching words in string data. This type of indexes is used in certain database managers.

5. Spatial: It facilitates the ability for performing operations in efficient manner on spatial objects. To perform this, the column should be of geometry type.

6. Filtered: A non clustered index. Completely optimized for query data from a well defined subset of data. A filter is utilized to predicate a portion of rows in the table to be indexed.