|
What is index? Define its types.Index
- 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.
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.What is index? Define its types.Index is created on a column in tables. It helps in providing fast access to data based on the values stored in the column.
E.g.: If an index is created on primary key of a table and then search for a row based on primary key value then SQL Server first finds that value in the index and then uses the index to locate the row in the table instead of performing a complete table scan.
Types of indexes:
- Clustered: Stores the actual row at the leaf level of the index.
- Nonclustered: Stores only the values from the indexed column and row locators that point to the actual row at leaf level instead of the actual row itself.
- Composite: Index containing more than one column (max 16).
- Unique: Ensures the uniqueness of each value in the indexed column.
|
