Keys

Table keys are used to identify records, and to speed up filtering and sorting. Having too few keys may result in painfully slow inquiries and reports. However, each key incurs a processing cost, because the index containing the key must be updated every time information in a key field changes. Key cost is measured primarily in terms of increased index maintenance processing. There is also additional cost in terms of disk storage space (usually not significant) and additional backup/recovery time for the increased database size (sometimes very important).

When a system is optimized for processing speed, it is critical to analyze the SQL Server indexes that are actpe because that is where the updating and retrieval time are determined. The determination of the proper number and design of keys and indexes for a table requires a thorough understanding of the types and frequencies of inquiries, reports, and other processing for that table.

Every NAV table must have at least one key: the primary key. The primary key is always the first key in the key list. By default, the primary key is made up of the first field defined in the table. In many of the reference tables, there is only one field in the primary key and the only key is the primary key. An example is the Payment Terms table. Highlight Table 3 Payment Terms and click on the Design button then to see the Keys window, click on View | Keys:

The primary key must have a unique value in each table record. We can change the primary key to be any field, or combination of fields up to 16 fields totaling up to 900 bytes, but the uniqueness requirement must be met. It will automatically be enforced by NAV, because NAV will not allow us to add a record with a duplicate primary key to a table.

When we examine the primary keys in the supplied tables, we see that many of them consist only of or terminate in a Line No., an Entry No., or another data field whose contents make the key unique. For example, the G/L Entry table in the following screenshot uses just the Entry No. as the primary key. It is a NAV standard that Entry No. fields contain a value that is unique for each record:

The primary key of the Sales Line table shown in the following screenshot is made up of several fields, with the Line No. of each record as the terminating primary key field. In NAV, Line No. fields are assigned a unique number within the associated document. The Line No. combined with the preceding fields in the primary key (usually including fields such as Document Type and Document No., which relate to the parent header record) makes each primary key entry unique. The logic supporting the assignment of Line No. values is done within explicit C/AL code. It is not an automatic feature. The No. Series pattern documentation can be found at https://community.dynamics.com/nav/w/designpatterns/74.no-series.aspx:

All keys except the primary key are secondary keys. There is no required uniqueness constraint on secondary keys. There is no requirement to have any secondary keys. If we want a secondary key not to have duplicate values, our C/AL code must check for duplication before completing the new entry.

The maximum number of fields that can be used in any one key is 16, with a maximum total length of 900 bytes. At the same time, the total number of different fields that can be used in all of the keys combined cannot exceed 16. If the primary key includes three fields (as in the preceding screenshot), then the secondary keys can utilize up to thirteen other fields (that is 16 - 3) in various combinations, plus any or all of the fields in the primary key. If the primary key has 16 fields then the secondary keys can only consist of different groupings and sequences of those 16 fields.

Behind the scenes, each secondary key has the primary key appended to the backend. A maximum of 40 keys are allowed per table.

Database maintenance performance is faster with fewer fields in keys, especially the primary key. The same is true for fewer keys. This must be balanced against improved performance in processes by having the optimum key contents and choices.

Other significant key attributes include Key Groups and SQL Server-specific properties:

• A number of SQL Server-specific key-related parameters have been added to NAV. These key properties can be accessed by highlighting a key in the Keys form, then clicking on the Properties icon or pressing Shift + F4. We can also have those properties display in the Keys screen by accessing View | Show Column and selecting the columns we want displayed. The following screenshot shows both the Show Column choice form and the resulting Keys form with all the available columns displayed.
• The MaintainSQLIndex and MaintainSIFTIndex properties allow the developer and/or system administrator to determine whether or not a particular key or SIFT field will be continuously maintained or it will be recreated only when needed. Indexes that are not maintained minimize record update time but require longer processing time to dynamically create the indexes when they are used. This level of control is useful for managing indexes that are only needed occasionally. For example, a Key or SumIndexField Technology (SIFT) Index that is used only for monthly reports can be disabled and no index maintenance processing will be done day to day. If the month end need is for a single report, the particular index will be recreated automatically when the report is run. If the month end need is for a number of reports, the system administrator might enable the index, process the reports, then disable the index again: