Clustered and Non-Clustered Index

Board Infinity -

Introduction

Have you heard something like the index in DBMS? Do you know something about clustered and non clustered indexes? If not, then don't worry. Board Infinity is here to help you out with these doubts.

In this article, we will be covering clustered and non clustered indexes. We will discuss their features and their examples. We will also look at the advantages of using them. Records are now stored in databases, and indexes are used to retrieve records much more quickly. An index is a special key composed of one or more columns. Let us understand what those indexes are.

Clustered Index

A clustered index is an index that describes the physical configuration of the table records in a database. Since there is only one-way records can be kept physically in a database table, each table can only have one clustered index. It keeps the data in sorted order. Let us discuss its features.

Features

A clustered index has the following features:

  • Default Indexing Procedure
  • It can index using a single column or several columns.
  • The same table that contains real records also contains indexes.
  • It supports the capability for fragmentation.
  • supports using clustered indexes for scanning and seeking.
  • Key lookup functionality is supported.

Let us understand what the advantages of choosing clustered indexing.

Advantages

The advantages of choosing clustered indexing are:

  • Clustered indexes are the best option for range or group queries that are frequently used with the count, min, or max functions.
  • When using a clustered index, a pointer can jump directly to a specific location in the data so that you can read the data in order from there.
  • You can increase cache visits and decrease page transfers with the Clustered Index.
  • The index entry at the start of a range can be found using a location mechanism by clustered indexes.

Everything comes up with some limitations and drawbacks. So, let us discuss some of the disadvantages of clustered indexing.

Disadvantages

Here are some disadvantages of choosing clustered indexing:

  • Clustered indexes generate many constant pages splits, such as data and index pages.
  • Insert, deleting, and updating SQL queries in clustered indexes require more system resources and execution time.
  • The leaf nodes are where most of the records are found in the clustered index.

Non-Clustered Index

The physical arrangement of the records kept in the database table is not specified by a non-clustered index. A separate table houses the Non-Clustered Indexes. There may be many non-clustered indexes created for a single table as a result of the fact that they are maintained in a different table. Let us discuss its features.

Features

These are the characteristics of the non-clustered index:

  • Key-value pairs are the units of storage for table data.
  • Indexes can be made specifically for tables or views.
  • It has links to records in the clustered index.
  • Supports the functions for searching and index scanning.
  • It offers records secondary access.
  • Each index row of the non-clustered index contains a key-value pair that is non-clustered and a row identifier.

Let us understand what the advantages of choosing non-clustered indexing.

Advantages

The advantages of choosing non-clustered indexing are:

  • You can save administrative expenses by using non-clustered indexing instead of clustered indexes.
  • An RDBMS table might have a large number of non-clustered indexes. As a result, it can be used to generate several indexes.
  • With the use of a non-clustering index, you can quickly obtain data from the table.

Everything comes up with some limitations and drawbacks. So, let us discuss some of the disadvantages of non-clustered indexing.

Disadvantages

Here are some disadvantages of choosing non-clustered indexing:

  • You can keep data organized logically by using a non-clustered index. You cannot physically arrange data records in a non-clustered index.
  • A non-clustered index lookup becomes more expensive.
  • If the clustering key is changed, the non-clustered index must also be updated because it contains the clustering key.

Conclusion

We learned about clustered and non clustered indexes in this topic. An index is a special key created from one or more columns. The physical structure is the main emphasis of the Clustered Index. Logical Structure is the main focus of the Non-Clustered Index, and they are stored in a different table, whereas clustered indexes store data. Indexes that are clustered perform better than non-clustered indexes.