Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
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,548
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,188 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,548 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,188 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,548 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,188 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

News and Announcements

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 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