In this article, we learn about the Index and their types.
BASICS OF INDEX
- An index is created on columns in tables or views. If we create an index then index quickly search the record. Without the index, we can search for records by a table scan it affects performance.
- An index searches faster by using a Balanced Tree (B-Tree) Structure. In B-Tree Structure data divided into root node, Non-leaf nodes, and Leaf nodes.
- Clustered index and Non-Clustered index have the same B-Tree structure format.
PRIMARY INDEX AND SECONDARY INDEX:-
- A primary index is an index on a set of fields that includes the unique primary key. It does not contain duplicates. Also called a Clustered Index For example- Employee Id.
- The primary index divided into two types: Dense Index and Sparse Index.
- A secondary index is an index that is not a primary index and may have duplicates. For example- Employee Name.
- When the table is activated primary index is automatically created. If a large table frequently accessed we should create a secondary index for the table because it is not possible to apply primary index sorting.
CLUSTERED INDEX
- A Clustered Index stores the actual data at the leaf node.
- The Clustered Index values are sorted in ascending or descending order. Therefore, they do not consume extra storage.
- A Clustered Index is faster than the Non-Clustered Index since it doesn’t involve any extra lookup step.
- Clustered Index Scan retrieves all the rows from the table.
- Clustered Index Seek has actual data. It retrieves selective rows from the table.
- A table has no clustered index then it referred to as the heap.
NON-CLUSTERED INDEX
- The Non-Clustered Index does not contain actual data. It uses a clustered index for fetching the data.
- The leaf node of the non-clustered index contains the value from an indexed column and RowId that point actual data.
- A RowId point to the heap table if there are no indexes. If we have clustered index then RowId points to clustered index key and finds the records.
- The Non-Clustered Index can not be sorted value. But we can create more than one non-clustered index.
COLUMNSTORE INDEX
- Columnstore Index used in the OLAP system. This type of index stored index data in the column-based format.
- The benefits of column store indexes are they reduced storage costs and better performance.
- If we can write a basic aggregation query then that the entire query was satisfied by scanning the column store index.
CLUSTERED COLUMN STORE INDEX
- Clustered column store indexes are updateable and are always the primary storage method for their entire table.
- They cannot be combined with other indexes and they do not physically store columns in a particular order.
NON-CLUSTERED COLUMN STORE INDEX
- Non-clustered column store index is a subset of columns.
- They require extra storage to store a copy of a column in the index and are updated by rebuilding or partitioning the index.
- Non-clustered column store index can be combined with other tables and physically store columns in a particular order to optimize compression.
COVERING INDEX
- An index that contains all the required information to resolve the query is known as a Covering Index.
- Covering Index includes all the columns, the query refers to in the SELECT, JOIN, and WHERE clauses.
ADVANTAGES OF INDEX
- An index is used to speed up searching in the database.
- The index helps to reduce the total number of I/O operations.
- It can be used for sorting.
- By using a unique index we can uniquely identify the record.
DISADVANTAGES OF INDEX
- The index takes additional index space.
- To perform the indexing we need a primary key on the table with a unique value.
- Partition is not allowed for an index-organized table.
- Decrease performance on INSERT, UPDATE and DELETE because on each operation the indexes must also be updated.
If you are a newbie to database learning — SQL Server recommended is the following must-watch video: -
