top button

What are indexes and mention different types of indexes?

0 votes
9 views
posted Mar 9 by Sheetal Gawade

Share this question
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button

1 Answer

0 votes

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.

answer Mar 14 by Arunkumaarts
...