✏️ Explanatory Question
Answer with Explanation
Difference between Primary & Cluster index.
Index
Indices are created on a table to improve efficiency with data retrieval. They are added by using the table designer. An index is defined by using one or more fields as the lookup criteria. When you are determining which fields to use for the index, consider fields that are often searched by a range or query, fields that are frequently used in joins, or fields that are often used to order or group results.
The order of the columns in an index is important. For efficiency, an index should be organized from the most granular column (highest number of unique values) to the least granular column.
Primary index: It works on unique indexes. The data should be unique and not null. Retrieve data from the database.
Clustered Index: It works on unique and non unique indexes. retrieve data from the AOS.
|
Primary index - Specifies which field in the table should be used as the primary index and is used for database optimization purposes. Primary index - Provides a unique key to each record in a table. The property for Allow Duplicates must be set to No because multiple values with the same result cannot exist. An example of a primary index is a phone number or account number. |
  |
|
Cluster index - Specifies the field by which the table should be organized. This property should never be left blank. Clustered - This type of index organizes the data for a table according to the order of the index. For example, the data in a phone book is first sorted by last name and then by first name. For each last and first name, there is a corresponding phone number. The clustered index in this case would be the last name. Non-clustered - This type of index provides a way to quickly reference data that is found in the clustered index by using a specified set of columns. An example of this is the index at the back of a textbook. You can look up a specific topic, and the index provides a list of page numbers that have information on that topic. |
The advantages of having a cluster index are as follows:
- Search results are quicker when records are retrieved by the cluster index,
- especially if records are retrieved sequentially along the index.
- Other indexes that use fields that are a part of the cluster index might use less data space.
- Fewer files in the database; data is clustered in the same file as the clustering index. This reduces the space used on the disk and in the cache.
- The disadvantages of having a cluster index are as follows: lt takes longer to update records (but only when the fields in the clustering index are changed).
- More data space might be used for other indexes that use fields that are not part of the cluster index if the clustering index is wider than approximately 20 characters).