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?
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
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.
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?
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.
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?
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.
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.
Many thanks Nikolaos. As you suggested, I added partition,dataareaid and moved them down the list and now my query runs perfect from SSMS.
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?
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.
5. Synchronize the table for the changes to take effect.
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.
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.
Oh. I thought you meant field order within an index and not sort order (ascending/descending) of field within index. Thanks for clarifying Martin.
Business Applications communities