Briefly explain different types of index
Answers
Answer:
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.