What are indexes?

• Index can be thought as index of the book that is used for fast retrieval of information.
• Index uses one or more column index keys and pointers to the record to locate record.
• Index is used to speed up query performance.
• Both exist as B-tree structure.
• Kind of the indexes are clustered and non-clustered.

Index is the way to order the records in a database according to the field values. It is the way to have fast access to the particular information. Indexes are created to the columns that are queried frequently.

What are the types of Indexes?

• Indexes can be clustered and non-clustered indexes.
• Clustered index keeps all the records in the database in the order of clustered index key.
• There can be only one clustered index per table.
• Non-clustered indexes are stored as B-tree structure in their own storage separate from data storage in the table

What are the disadvantages of the Indexes?

• Indexes improve query performance but it slows down data modification operations.
• Indexes consume disk space.

What are indexes?

An index of a data base is a data object or structure which is utilized for improving the operations speed in a specific table. Index is a subset of columns from a table. The values in a subset are stored in a sorted order and the database server can quickly finds the records which are based on the data in the index.

 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.