Keygroups have been available in Dynamics NAV for some time now, but since the release of Dynamics NAV 5.0 some new functions can be used to enable and/or disable these key groups via CAL code:

• [Ok :=] KEYGROUPENABLE(GroupName)

  Use this function to enable a key group.

• [Ok :=] KEYGROUPDISABLE(GroupName)

  Use this function to disable a key group.

• [Ok :=] KEYGROUPENABLED(GroupName)

  Use this function to check whether or not a specific key group is enabled.

When you have a look in the design of a table and open the list of Keys of that table you will notice that every Key has a KeyGroup property. You can see this property to specify the predefined key groups that a key belongs to. After you have assigned a key to one or more key groups, you can selectively activate or deactivate the key by enabling and disabling the key groups. In the past this was only possible by going into the Database Key Groups window.

To enable or disable a key group for sorting, click File, Database, Information and the Database Information window appears:

Then click Tables, Key Groups and the Database Key Groups window appears. You can then enable or disable a key group by clicking the appropriate button:

Unfortunately, it is very difficult to get an overview of the keys and tables that are affected when you enable or disable a key group. However, you can look at the design of any table and see the keys that have been defined for that table and whether or not the keys belong to a key group.

Adding a large number of keys to database tables decreases performance. However, by making the keys members of predefined key groups you can have the necessary keys defined and only activate them when they are going to be used.

And as from Dynamics NAV 5.0 we now have the possibility to do this in the CAL code.

This can be useful for reports that use certain keys but that are executed very rarely, for example only once a year. By only enabling these keys when they are needed you can avoid the performance overhead of maintaining them when they are not used.

Remarks:

Be careful with this. When you disable a key, it is no longer available and can also not be used in code. Disabling a key is like deleting it; the difference is that it is still in the list of keys, but disabled.

When working on Sql Server, this means that if that key had any SumIndexField(s) attached to it, the related SIFT tables will also be removed if you disable the key (and recreated when you enable the key).

Enabling a key, means that the index table has to be rebuild, and so this can cause performance issues and locking, especially when there are many records in the table.

This is not to be used as an alternative for not maintaining a key on Sql Server. The MaintainSqlIndex and MaintainSIFTIndex/SiftLevelsToMaintain properties are meant for that purpose.

Comments

[Buy Used Phones]

I am getting an error when I try to click the "Key Groups" button. Is there any way to fix this?