Finance | Project Operations, Human Resources, ...

Link between primary index and cluster index in a table for performance improvement

(1) Share

Report

Posted on by John Bright

145

,Hi experts,

I am trying to understand how indexing would increase performance in tables and should primary index and cluster index should be the same in order to get the performance improvement?

The reason that I am asking whether it is linked is, I read somewhere stating that /Indexing will increase the speed of fetching the record from a table by using binary search/. My question here is, if primary key is given as xxxId should cluster index should be xxxId as well? Since binary search works only when the records are arranged in ascending order. If we don't arrange the records in a table in the order of primary index, will there not be any performance improvement unless we use the cluster index as index to search in select statements? I don't even know my understanding of increasing performance through binary search is right. Any inputs is appreciated.

Thanks in advance,

John

I have the same question (0)

All responses (4)

Answers (0)

Sort by

Suggested answer

Waed Ayyad 9,214 Super User 2026 Season 2 on at

Like (0)

Report
Copy link

Link copied!

Hi,

Primary indexes are unique indexes and clustered may or may not be, it isn't necessary to have cluster index the same as the primary index and the cluster index depends on the physical order of the records on DB.

You can check the below link about indexes:

https://mfmujahidmim.wordpress.com/2015/08/27/primary-key-indexcluster-index-non-cluster-index-in-ax-2012/

Thanks,

Waed Ayyad

If this helped, please mark it as "Verified" for others facing the same issue.

Was this reply helpful? Yes No
Suggested answer

Anthony Blake 3,198 Super User 2026 Season 2 on at

Like (0)

Report
Copy link

Link copied!

At a high level, the clustered index is the physical order of the data - hence why you can only ever have one. Primary and clustered index being the same, such as in your example by ID, while there are of course other factors, should increase performance when querying data by ID. The primary index is used for looking up data, and if that index is looking directly at the table rather than referencing a separate index with pointers to table locations, there should be an improvement. The results will depend on other factors though, so I would copy data to an environment where I could do some benchmarking to discover the impact. You can take a look at the SQL execution plans in SSMS and see the difference.

Was this reply helpful? Yes No
John Bright 145 on at

Like (0)

Report
Copy link

Link copied!

Hi Anthony,

Thanks for your response.

I did a bit of analysis in SSMS from my side. Here is what I found.

Basically, I took two tables one is the standard "PurchTable" and other is a custom table that I developed "xxxTable". I have given primary index and cluster index in my custom table the same xxxId as it was purchId in terms of PurchTable. I did some select statements and took a look at the execution plan. The thing that I found is, it says ordered as false in my custom table but it is true in the PurchTable. But if I just select all data in my custom table, it is actually ordered but the execution plan says it is not. I don't understand why!

Apart from this analysis what I am trying to understand is. Indexing improves performance because it performs binary search on querying data? What happens behind the scenes in sql.

Was this reply helpful? Yes No
Suggested answer

Raj Borad 1,578 on at

Like (0)

Report
Copy link

Link copied!

Hi John,

The primary index of the table is by default RecId of the Table. You can also change it.

As per my knowledge, At the time of extracting the data, Primary index column sorts first then Cluster index column.

As you see in above SS of ProjItemTransCost Table.

InventTransId which is (Primary Index) is different/unique for all the Records in the table but the ProjId is/may be the same.

Thank you,

Raj D Borad

Was this reply helpful? Yes No