How using SQLIndex property can affect which index NAV uses

  • Article

The usual rule for specifying a key in NAV is, that it will chose the first key that matches all the fields specified by SETCURRENTKEY, RunformLink, etc.

Example:
Table 21 "Cust. Ledger Entry" has the following keys (not all of them listed):
 
  - Entry No.
  - Customer No.,Posting Date,Currency Code
  - Customer No.,Open,Positive,Due Date,Currency Code
  - Customer No.,Applies-to ID,Open,Positive,Due Date
 
You have code like this:
CustLedgEntry.SETCURRENTKEY("Customer No.");
CustLedgEntry.FINDFIRST;
  
You will get a query with this ORDER BY:
ORDER BY "Customer No_","Posting Date","Currency Code","Entry No_"
 
So far, all works as expected: NAV finds the first key that matches the sorting, and uses that.
 
 
This changes if you change the SQLIndex property on the key. If we design table 21 and set SQLIndex = "Customer No." on the key used above, then NAV will skip this key and use the next matching one, and the code above will now generate this ORDER BY:
ORDER BY "Customer No_","Open","Positive","Due Date","Currency Code","Entry No_"
 
So now, even if we want to sort by "Customer No.", and we have a SQL Index which is exactly that, NAV chooses a different index. And it no longer follows the rule of choosing the first key available which satisfies the requested sorting.
 
So specifying SQLIndex on a key makes this key less likely to be chosen by NAV. Of course, this does not affect which index SQL Server actually decides on when it makes its query plan. It only afects the ORDER BY clause.
 

The reason for this is:

Above is mentioned that we want to sort on "Customer No.". This is not the whole truth. NAV always adds the primay key, so actually we want to sort on "Customer No.", "Entry No.". The primary key fields are used for specifying a sorting (ORDER BY) that is deterministic and they are also used to construct SQL for calls to Record.NEXT(), which might be needed to reposition itself in the database. This happens a lot from the UI but also from C/AL code.

So the short story is: When SQLIndex is used, they key will by definition not match the ORDER BY which is based on the NAV key, and the index is likely to not be unique, so NAV will give the key a lower priority when there are other similar keys which do not have SQLIndex specified.

Only when a SQL index is NOT specified, NAV automatically adds the primary key field(s) when it creates the index on SQL server. When we do add SQLIndex, this does not happen - the SQL Index will be exactly what you specify in this property.
 
In this example where we set SQLIndex to "Customer No.", the SQL Index will just be just that - "Customer No_", so it will not be unique and it will not satisfy an ORDER BY on “Customer No.,Posting Date,Currency Code, Entry No.” and it will not satisfy calls to Record.Next(). So when SQLIndex is specified, NAV will continue down the list of keys, looking for the next key that matches the chosen sorting (and does not have SQLIndex specified) and use that instead.
 
 
It’s recommended to use the SQLIndex property with great caution. Also see previous blog posts:
Beware the SQL Index property on NAV 5.0 SP1 
SQLIndex property
 
If you do use it, then also be aware of the changes in behaviour described here
 
 

 

Lars Lohndorf-Larsen

Microsoft Dynamics UK

Microsoft Customer Service and Support (CSS) EMEA

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.