Why do we need so many table keys in NAV/BC

samantha73

Hi Guys
I have looked at couple of tables to understand the base app allocation of table keys, for example, sales line table contains 19 keys for all possible combinations to retrieve data. Is this the norm when creating your own tables as well? For example I created a table with say DocType, Number, Customer and possible data retrievals as keys
Key1 (DocType, Number, Customer)
Key2 (Customer, DocType,Number)

To me it looks like NAV doesn't bother with the performance hit from write functions

RockWithNAV

No mam you should not be making any keys without a proper cause. Standard base tables have many keys and it has a very strong reason behind, some are made for sorting purpose, some for SIFT technologies usability(CALCSUM) and other reasons. You cannot make more then 40 active keys in a table so make sure you have a reason for every keys you add.

DenSter

it's left over from the olden days, where you could not sort without a key, and something called SIFT. Both of those are now possible without keys (you still need to be smart about it though because if done right keys will help performance)

RockWithNAV

No mam you should not be making any keys without a proper cause. Standard base tables have many keys and it has a very strong reason behind, some are made for sorting purpose, some for SIFT technologies usability(CALCSUM) and other reasons. You cannot make more then 40 active keys in a table so make sure you have a reason for every keys you add.

DenSter

it's left over from the olden days, where you could not sort without a key, and something called SIFT. Both of those are now possible without keys (you still need to be smart about it though because if done right keys will help performance)

samantha73

Thanks DenSter..I thought something like "left over from olden days" got to be another reason as I'm comparing NAV to other ERP applications, I cannot see so many keys in other applications

DenSter

Don’t knock it for SQL lots of keys is counterproductive but the proprietary database was actually lightning fast, I would bet against SQL any day.

Roelof

If all alternate keys are disabled, would SQL still be fast enough? I'm asking since I came across an issue during an upgrade where I got a dependency issue during syncing. I had to disable keys to get rid of the conflict. So, I'm wondering to bring them back since the upgrade is for a large database (0.75 Terabyte) and it might take some time to enable them again. Any ideas?