SBX - Search With Button

SBX - Forum Post Title

Index order in AX 2012

Microsoft Dynamics AX Forum

MYGz asked a question on 13 Aug 2018 1:23 AM
My Badges

Question Status

Verified

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?

Reply
Nikolaos Mäenpää responded on 13 Aug 2018 1:35 AM
Verified Answer

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

Reply
Martin Dráb responded on 13 Aug 2018 1:36 AM
Verified Answer

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.

Reply
MYGz responded on 13 Aug 2018 1:59 AM
My Badges

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?

Reply
Nikolaos Mäenpää responded on 13 Aug 2018 2:09 AM
Verified Answer

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.

Reply
MYGz responded on 13 Aug 2018 2:24 AM
My Badges

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?

Reply
Nikolaos Mäenpää responded on 13 Aug 2018 2:31 AM
Verified Answer

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.

Reply
Martin Dráb responded on 13 Aug 2018 2:32 AM
Verified Answer

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.

Reply
MYGz responded on 13 Aug 2018 2:33 AM
My Badges

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

Reply
MYGz responded on 13 Aug 2018 2:45 AM
My Badges

Thanks Martin.

Reply
Bruce responded on 13 Aug 2018 10:28 PM

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

Reply
MYGz responded on 14 Aug 2018 12:59 AM
My Badges

Hi Bruce,

Nikolaos already answered this above.

Follow these steps:

1. Select the index.

2. Press and hold alt key.

3. And then press up or down arrow keys to change the order.

4. Save.

5. Synchronize the table for the changes to take effect.

Reply
Martin Dráb responded on 14 Aug 2018 1:07 AM

If you mean that an index field should be sorted from Z to A instead of from A to Z, it's a different thing than what we discussed here. And unfortunately I don't think it's possible.

Reply
Bruce responded on 14 Aug 2018 1:32 AM

Thanks Mygz and Martin for your response and yes, that's what I meant making Field1 (A-Z), Field2(Z-A), Field3(A-Z) inside an Index.

Reply
MYGz responded on 14 Aug 2018 1:36 AM
My Badges

Oh. I thought you meant field order within an index and not sort order (ascending/descending) of field within index. Thanks for clarifying Martin.

Reply
Nikolaos Mäenpää responded on 13 Aug 2018 1:35 AM
Verified Answer

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

Reply
Martin Dráb responded on 13 Aug 2018 1:36 AM
Verified Answer

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.

Reply
Nikolaos Mäenpää responded on 13 Aug 2018 2:09 AM
Verified Answer

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.

Reply
Nikolaos Mäenpää responded on 13 Aug 2018 2:31 AM
Verified Answer

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.

Reply
Martin Dráb responded on 13 Aug 2018 2:32 AM
Verified Answer

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.

Reply

SBX - Two Col Forum

SBX - Migrated JS