PrimaryIndex /clusteredIndex values vs surrogate key

(0) Share

Report

Posted on by junior AX

1,552

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?

I have the same question (0)

All responses (5)

Answers (1)

Suggested answer

Martin Dráb 238,149 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

It's recommend to use the surrogate key there and use a natural key as the replacement key.

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Martin Dráb 238,149 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
There are two main reasons for using surrogate keys:

Using a single numeric field is more efficient than using something like a combination of DataAreaId and another string field.

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.

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Verified answer

Martin Dráb 238,149 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No

Join the conversation

Community site session details

PrimaryIndex /clusteredIndex values vs surrogate key

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

PrimaryIndex /clusteredIndex values vs surrogate key

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates