[Database] Types of Indexes

An index is a structure within database that is used to quickly retrieve specific rows from a table. An index contains keys built from one or more columns in the table, and the general structure of an index is B-Tree.

Clustered index

• Each table can only have one clustered index.
• Table would sort and store data rows according to clustered index.
• If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
• A primary key is a unique index that is clustered by default in most of databases, though it may slow down writes if your primary key is not a sequential number.

Nonclustered index

• An index structure separate from the data stored in a table, so each table can have more than one nonclustered indexes.
• Since nonclustered index stored in another table, it cause extra space.
• Nonclustered indexes would store the value of the clustered index key when a table has a clustered index.
• Nonclustered indexes would store the value of the RID (Row Identifier) when a table is a heap.

Covering index

• Use INCLUDE clause to include columns that need to cover.
• Retrieve data directly if columns in query match with the columns in covering index without reaching base table to reduce the disk I/O operations.
• Covering index only store specific data according to columns, so its size is less than clustered index.

Composite index

• Use more than one column to build index, and store additional sorted pointers to other columns.
• The query optimizer would follow Left-Prefix index rule, and use all columns in the composite index, or the first columns, the first two columns, and so on for queries.
• The column order of a composite index has great impact on its usability, because it will use this order to build B-Tree.
• Composite index include different set of indexes according to the order, so it can reduce disk space.
• Since composite index store pointers to other columns, it has ability to move through the data quicker, and reduce the disk I/O operations.