Significance of keys

jangid2k3

What is the significance of keys in Navision.
What fields should be included in a key?

Regards
Manjeet

rthsw

If you use the native server (and don't want to change to sql), you can set the keys only for your needs. Even Boolean and optionfields are usable at every place.
The primary key has to be allways unique, so the rules are only defined by your design.

if you use (or plan) to use the SQL-Option, you should pay attention on taking the most selective fields in front of the key, otherwise the Optimizer (and the Query-enginge) of the Server will get problems or use wrong keys.
Reason: The SQL analyse the request, and tries to reduce the resultset at the early possible way.

Example: Take a look at the T21. If you use a key "Open, Customer No.,....", the first field will divide the resultset in max 1/2 of the tablesize (no matter about the usage). If you filter on Open=true, you might get a resultset of 10% (instead of the estaminated 50%). Navision Native uses Jumps on the query, so he can give you the needed 10% very very quickly. SQL tries to switch to the field Customer (or some other key!), and rebuilds the sorting for open on the resultset, so he "thinks", he will part the resultset into very little peaces (because of the Customer No.), but needs to read much more rentrys than the native does. So the better Key could be start with Customer No., Open or Outstanding Amount (is not, is calculated!) or something else.

On SQL, you will allways have to make Query-analyses on your used keys, and they can change their behavement on growing Data. So SQL will allways need Supervising over the Time. Native does not.