Finance | Project Operations, Human Resources, ...

find method and index hint

(0) Share

Report

Posted on by junior AX

1,552

Hi,

If I have a table where primary key property is surrogate key. And I have an index with duplicate key /No/ called Idx, where it contains Field1 and Field2.

1. When i create the find method, should my select contain IndexHint Idx or shall it be a normal select with a where condition?

public class Table1 extends common{    static Table1 find(Enum1   _field1,        str _field2, boolean       _forUpdate = false)    {        Table1 table1;        if (table1)        {            if (_forUpdate)            {                table1.selectForUpdate(_forUpdate);            }            select firstonly table1                    index hint Idx                    where table1.Field1 == _field1                        && table1.Field2 == _field2;        }        return table1;    }}

2. Should the Primary key stay as Surrogate key or should it be Idx?

All responses (5)

Answers (0)

Sort by

Anton Venter 21,231 Super User 2026 Season 2 on at

Like (0)

Report
Copy link

Link copied!

Hi,

The keyword IndexHint suggests a hint but it actually forces the specified index (by the IndexHint keyword) to be used by SQL Server. If you don't use IndexHint, SQL Server will decide which index is the best to use. SQL Server will therefore use the primary key specified on your table unless IndexHint is used.

If your table has a single field primary key, then you can change the Primary Key field property to that field. In your case you have two fields, I would keep SurrogateKey and set ClusteredIndex to this Field1/Field2 index. How many records will be in your table?
GirishS 27,843 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi Junior,

If your table has only one index, then I think it's not necessary to specify index hint in find method.

For second question if your table has only one unique index you can specify your custom index in Primary key property otherwise it should be surrogate key.

Thanks,

Girish S.
Suggested answer

Martin Dráb 240,748 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!
Index hints in F&O are disabled by default, therefore just adding an index hint statement has no effect. You shouldn't normally use these hints (although there may be cases when it may be needed).

You can learn more about it in:

New, Changed, and Deprecated Features for AX 2012 > Deprecated features > Deprecated: X++ index hint clause (https://learn.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/deprecated-x-index-hint-clause)

What's new or changed in Dynamics 365 for Finance and Operations platform update 23 (January 2019) > Enabling index hints in X++ again (https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/fin-ops/get-started/whats-new-platform-update-23#enabling-index-hints-in-x-again)
junior AX 1,552 on at

Like (0)

Report
Copy link

Link copied!
Hi Anton,

Field1 and Field2 are unique -- does that mean i need to put surrogate key? but why if more than one unique field we need to use surrogate? and why if it's only one single field then we need to put it as primary key?

I have 4 other fields other than Field1 and Field2

Since i have field1 and field2 as unique... why i should put clustered index based on them?? and if it was only one unique field, should clustered be surrogate in this case?

Hi Girish,

Same question as Anton: Field1 and Field2 are unique -- does that mean i need to put surrogate key? but why if more than one unique field we need to use surrogate? and why if it's only one single field then we need to put it as primary key?

I think you all agreed that i need to put normal select without index hint -- so thanks Martin, Girish and Anton
Martin Dráb 240,748 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Please don't forget to verify an answer if you get one. You're getting many answers but you haven't verified any for two months.