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.
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.
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.
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.
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.
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.