Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / PrimaryIndex /clustere...
Finance | Project Operations, Human Resources, ...
Answered

PrimaryIndex /clusteredIndex values vs surrogate key

(0) ShareShare
ReportReport
Posted on by junior AX 1,550
Hi,
 
As we know, there are two properties that we can fill, PrimaryIndex and clusteredIndex. And by default they are filled with surrogate key.
 
My question is what is better? To fill those two properties with the allow duplicate No index or keep it as surrogate key?
 
Does it depened on the number of fields inside the unique index? If it's one or more?
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,466 Most Valuable Professional on at
    PrimaryIndex /clusteredIndex values vs surrogate key
    1) Let's say you want to change an account number of a customer from Old1 to Renamed1.
    2) A natural key is key intended for human understanding, such as Order1 instead of 4878897844. Please read Table Keys: Surrogate, Alternate, Replacement, Primary, and Foreign; terminology is one of things covered there (although just briefly).
    3) DataAreaId wasn't the only argument.
    4) Which order of records (defined by the clustered key) is the best depends on how the table is used. The surrogate key is a good candidate (never changing, new values added at the end) but it doesn't mean that no other option is possible. If you want to learn how to use clustered indexes, read Effective Clustered Indexes, for instance.
  • junior AX Profile Picture
    junior AX 1,550 on at
    PrimaryIndex /clusteredIndex values vs surrogate key
    Hi Martin,
     
    1. What do you mean rename primary key? If i changed the content of primary key like adding a new field or replacing one, why would i rename it? Sorry i didn't understand?
     
    2. And by natural key you mean allow duplicate No index?
     
    3. You said surrogate key is better than combination of dataAreaId and another field. What if the table has save data per company as No. Would surrogate key still be better? And why?
     
    4. Just to make sure, your explanation also for clustered index? I mean surrogate key is always better?
  • Martin Dráb Profile Picture
    Martin Dráb 230,466 Most Valuable Professional on at
    PrimaryIndex /clusteredIndex values vs surrogate key
    There are two main reasons for using surrogate keys:
    1. Using a single numeric field is more efficient than using something like a combination of DataAreaId and another string field.
    2. You don't ever need to change the surrogate key, therefore you don't have to deal with all the problems with "Rename primary key". When needed, you'll change the natural key.
    The reason why tables like CustTable don't use surrogate keys is because they're created years before surrogate keys where added to Dynamics AX.
  • junior AX Profile Picture
    junior AX 1,550 on at
    PrimaryIndex /clusteredIndex values vs surrogate key
    Hi Martin,
     
    Can you please explain why surrogate key is recommended? Is it the same explanation for both primary and clustered?
     
    Is there no case where we should the use the unique index instead?
     
    And why we can see standard tables like custTable using AccountNum as primary index and not surrogate key?
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 230,466 Most Valuable Professional on at
    PrimaryIndex /clusteredIndex values vs surrogate key
    It's recommend to use the surrogate key there and use a natural key as the replacement key.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,735 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,466 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans