What is the difference between Primary index and clustered index in AX Dynamics 365

Difference Between Primary index and Clustered index in AX Dynamics 365

There are two types of indexes

1. Unique or Primary

2. Clustered Index or Non-Unique

1. Primary index:

Primary index identify records uniquely in any given table.  It will not allow any duplicates records for  the particular field in a table. The Primary Index (Unique index) can be create either on one field (column) or multiple fields (columns). The data in Unique index should be unique and not null. Each Unique combination of primary key fields uniquely identifies one (and only one) record in the table. 

Primary Key must be Unique, There can be only one primary key for a table. But all unique indexes are not primary keys.

Primary index used to retrieve data from data base, and used to organize both data store and other indexes for more efficient updating and faster access.

2. Clustered Index:

Clustered or Non-Unique indexes are created for performance advantages. Clustered index or Non unique indexes defines the order in which data is stored physically in a Table.

The general rules for creating clustered index:

• If only one index is created on the table, make it clustered. 

• Create a clustering index on the key on all group and main tables.

The advantages of clustered indexes as follow

• 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:

• It 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).

here are some tips on how to answer the question "What is the difference between cluster index and index in D365FO interview?"

• Start by defining what a cluster index and an index are. A cluster index is a special type of index that determines the physical order of the rows in a table. An index is a data structure that helps the database find rows quickly.
• Explain the key differences between cluster indexes and indexes. The main difference is that a cluster index is the only type of index that can be used to physically order the rows in a table. Indexes can be used to improve the performance of queries that search for specific values or ranges of values.
• Give examples of when to use a cluster index and when to use an index. A cluster index should be used on the primary key of a table. Indexes can be used on other columns as well, but they are most effective when they are used on columns that are frequently used in queries.
• Discuss the advantages and disadvantages of using cluster indexes and indexes. Cluster indexes can improve the performance of queries that search for specific values or ranges of values. However, they can also make it more difficult to insert, update, and delete rows in a table. Indexes can improve the performance of queries, but they can also take up space in the database.

Here is an example of how you could answer the question in an interview:

"A cluster index is a special type of index that determines the physical order of the rows in a table. This means that the rows in a table are stored in the same order as the index. Indexes are data structures that help the database find rows quickly. They do this by storing the values of one or more columns in a table and the corresponding row ID. When a query is executed, the database can use the index to find the rows that match the query criteria.

The main difference between cluster indexes and indexes is that a cluster index is the only type of index that can be used to physically order the rows in a table. This means that there can only be one cluster index per table. Indexes can be used on any column in a table, and there can be multiple indexes on a table.

Cluster indexes are most effective when they are used on columns that are frequently used in queries. For example, if you have a table of customers and you frequently query the table by customer ID, you would want to create a cluster index on the CustomerID column. This would improve the performance of the queries because the database would be able to find the rows quickly.

Indexes can also improve the performance of queries that search for specific values or ranges of values. For example, if you have a table of products and you want to find all products that are priced between $100 and $200, you could create an index on the Price column. This would improve the performance of the query because the database would be able to find the rows quickly.

However, there are also some disadvantages to using cluster indexes and indexes. Cluster indexes can make it more difficult to insert, update, and delete rows in a table. This is because the database has to physically move the rows around when the index is updated. Indexes can also take up space in the database.

Overall, cluster indexes and indexes can be a valuable tool for improving the performance of queries. However, it is important to use them wisely and to be aware of the potential disadvantages."

I hope this helps!