Microsoft Dynamics AX (Archived)

Index order in AX 2012

(0) Share

Report

Posted on by MYGz

2,162

Hi,

How is the order of index determined when more than 1 index is added to a group.

1. Is it in the order that we add, i.e descending order in the list, which means we have to add the columns in ascending order

2. The order it appears in the list, i.e ascending order in the list, which mean we should add the columns in descending order.

Can we change the order from AX? Or do we have to change the order from database side, i.e SSMS?

*This post is locked for comments

I have the same question (0)

All responses (14)

Answers (5)

Verified answer

nmaenpaa 101,160 Moderator on at

Like (1)

Report

What do you mean by adding an index to a group?

Do you mean adding fields to an index? If yes, you should usually add them in the most selective order. For example in SalesLine table you would want to add SalesId first, then LineNum.

Or do you mean adding multiple indexes to a table? You can and in many cases should have multiple indexes. SQL Server will determine the index that it will use for each incoming query. So in most of the cases you don't need to worry about which index will be used. The order of the indexes in the table has no impact on how the indexes will work.

If you want to change the order of the fields in an index, you must do it in AOT. Then synchronize the table, and the changes will be synchronized to the database.

Here is an article about SQL Server indexes in general: docs.microsoft.com/.../sql-server-index-design-guide

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,874 Most Valuable Professional on at

Like (1)

Report

I assume you mean columns in a single index, not indexes in a group (I'm not familiar with any concept of index groups).

It's in the order in which they're in the list. When you add another field, it's added at the end by default, but you can move it at another position. This is done in AOT.

Database synchronization will create in database what you've defined in AOT, therefore the order in database is the same as in AOT.

Was this reply helpful? Yes No
MYGz 2,162 on at

Like (0)

Report

Thank you Nikolaos and Martin for the valuable inputs.

Yes I meant adding fields(columns) to an index.

After synchronisation this is the order in AOT:

This is the order in Database:

How can I change order in AOT?

AX is adding additional fields (dataareaid,partition) to the index.

As I am working on an integration project, I have to do a lot of querying from the database on staging tables and ax tables and I don't want those additional fields (dataareaid,partition).

What I can do is create an index from database side and use it but then I am not able to synchronize table in AX.

Is there a way to use an index that will work from both ends, i.e ax and database?

Was this reply helpful? Yes No
Verified answer

nmaenpaa 101,160 Moderator on at

Like (1)

Report

Your screenshots show two different indexes.

In AOT you show JournalNumNamePostedIdx.

In SQL Server you show PostedJournalNumIdx.

You can change the field order in AOT with selecting the field you want to move, and then Alt-Up and Alt-Down will move the field.

By default, the first fields of an index are Partition and DataAreaId since they are usually the most selective fields.

You can change this by adding Partition or DataAreaId field in your index in AOT. After that it will be placed in the position where you put it. So if you want to, you can move them to the end of the index.

You can't exclude those fields from the index, they will be always there. Having to do a lot of querying is not a reason to drop fields from indexes. Indexes are there because of performance, and performance should be the primary concern when designing indexes.

Was this reply helpful? Yes No
MYGz 2,162 on at

Like (0)

Report

My bad took the wrong index from DB, it has similar name and same fields, corrected now.

One last question. Would that be right to create 2 indexes having same fields(columns) but in different order because of different query requirements?

Was this reply helpful? Yes No
Verified answer

nmaenpaa 101,160 Moderator on at

Like (1)

Report

Yes, that is perfectly ok, and in some cases necessary.

For example you might have a table which contains names of people. You could have two indexes:

1) Last name, first name -> Good for searching by last name

2) First name, last name -> Good for searching by first name

But please remember that each index adds overhead to the insert and update operations.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,874 Most Valuable Professional on at

Like (1)

Report

I don't think such indexes would be very useful for selections, because DBMS can happily use an index with fields in a different order than in which you've put them to a query.

But it may be beneficial for sorting.

Was this reply helpful? Yes No
MYGz 2,162 on at

Like (0)

Report

Many thanks Nikolaos. As you suggested, I added partition,dataareaid and moved them down the list and now my query runs perfect from SSMS.

Was this reply helpful? Yes No
MYGz 2,162 on at

Like (0)

Report

Thanks Martin.

Was this reply helpful? Yes No
jdev 10 on at

Like (0)

Report

Hi,

I have a almost the same issue. The requirement is to add Index fields where some fields are in descending order. Is there a way to do this in AX?

Thanks

Was this reply helpful? Yes No