Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Suggested answer

find method and index hint

(0) ShareShare
ReportReport
Posted on by 1,550
​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?

  • Martin Dráb Profile Picture
    Martin Dráb 230,842 Most Valuable Professional on at
    find method and index hint
    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.
  • junior AX Profile Picture
    junior AX 1,550 on at
    find method and index hint
    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
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 230,842 Most Valuable Professional on at
    find method and index hint
    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:
  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    find method and index hint
    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.
  • Anton Venter Profile Picture
    Anton Venter 19,493 Super User 2025 Season 1 on at
    find method and index hint
    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?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,969 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,842 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans