Table keys

The database management system, which is SQL Server, uses keys to identify rows in a table. Keys identify the rows by combining one or more columns of a table. SQL also uses indexes to speed up data retrieval from rows in a table. This article explains how to create keys and indexes for Business Central tables from AL code.

Keys in AL

In AL, a key definition is a sequence of one or more field IDs from a table. You can define keys in table objects and table extension objects, depending on the type of key. There are two types of keys: primary and secondary.

• Primary keys

  A primary key uniquely identifies each record in a table. Every table has a primary key, and there can only be one primary key per table. Primary keys are defined on table objects only. In SQL, table extension objects inherit the primary key of the table object they extend (the base table object). So any key that you define in a table extension object is considered a secondary key.

• Secondary keys

  Secondary keys create indexes in SQL. They're defined in both table objects and table extension objects. You can define multiple secondary keys for a single table object and table extension object.

  A key in table extension object can include fields from the base table object or the table extension object. There are some limitations, however. For more information, see Limitations and restrictions.

Primary keys

The primary key keeps track of data in a table. The primary key is composed of up to 16 fields in a record. The combination of values in fields in the primary key makes it possible to uniquely identify each record. In AL, the first key defined in a table object is the primary key. The primary key determines the logical order in which records are stored, no matter the physical placement of the fields in the table object.

Logically, records are stored sequentially in ascending order and sorted by the primary key. Before adding a new record to a table, SQL Server checks if the information in the record's primary key fields is unique. If so, it then inserts the record into the correct logical position. Records are sorted dynamically so the database is always structurally correct. This sorting allows for fast data manipulation and retrieval.

The primary key is always active. SQL Server keeps the table sorted in primary key order and rejects records with duplicate values in primary key fields. That's why the values in the primary key must always be unique. It's not the value in each field in the primary key that must be unique. Instead, it's the combination of values in all fields that make up the primary key.

Secondary keys

In a table object, any keys defined after the primary key are called secondary keys. All keys defined in a table extension object are considered secondary keys.

A secondary key is implemented on SQL Server using a structure that is called an index. This structure is like an index that is used in textbooks. A textbook index alphabetically lists important terms at the end of a book. Next to each term are page numbers. You can quickly search the index to find a list of page numbers (addresses), and you can locate the term by searching the specified pages. The index is an exact indicator that shows where each term occurs in the textbook.

When you define a secondary key and mark it as enabled, an index is automatically maintained on SQL Server. The index reflects the sorting order that is defined by the key. Several secondary keys can be active at the same time.

A secondary key can be disabled so that it doesn't occupy database space or use time during updates to maintain its index. Disabled keys can be re-enabled, although this operation can be time-consuming because SQL Server must scan the whole table to rebuild the index.

The fields that make up the secondary keys don't always contain unique data. SQL Server doesn't reject records with duplicate data in secondary key fields. So if two or more records contain identical information in the secondary key, SQL Server uses the table's primary key to resolve this conflict.

Unique secondary keys

A key definition includes the Unique property that you can use to create a unique constraint on the table in SQL Server. A unique key ensures that records in a table don't have identical field values. With a unique key, when table is validated, the key value is checked for uniqueness. If the table includes records with duplicate values, the validation fails. Another benefit of unique indexes is providing information to the query optimizer that helps produce more efficient execution plans.

You can create unique secondary keys that are composed of multiple fields, like with primary keys. In this case, it's the combination of the values in the secondary key that must be unique. Consider the Customer table, for example. Suppose you wanted to make sure there are no customers that have the same combination of values for the Name, Address, and City fields. You could create a unique key for these fields.

Unlike primary keys, it's possible to define multiple unique secondary keys on a table.

System keys

There's always a unique secondary key on the SystemId field.

Secondary keys with included fields

INTRODUCED IN: Business Central 2021 release wave 2

With non-clustered secondary keys, you can use the IncludedFields property to add fields that aren't part of the key itself. In SQL server, these non-key fields correspond to what are called included columns. Using included fields lets you create indexes that cover more queries, and lets you bypass the maximum number of fields in a key.

A secondary key with included fields can improve SQL query performance, especially when SQL index contains all columns in the query, either as key columns or included columns. The performance improves because the query optimizer can locate all the column values within the index. And, it doesn't access table or clustered index data, which results in fewer disk I/O operations. For more information about included columns in SQL, see Create indexes with included columns.

Non-clustered Columnstore keys

INTRODUCED IN: Business Central 2021 release wave 2

Non-clustered columnstore indexes (sometimes referred to as NCCIs) are supported on tables.

With the ColumnStoreIndex property, you create a non-clustered columnstore index on the table in SQL server. Using a non-clustered columnstore key can improve query performance when doing analytics on large tables. This index type uses column-based data storage and query processing to achieve gains up to 10 times the query performance in analytical queries over traditional row-oriented storage. You can also achieve gains up to 10 times the data compression over the uncompressed data size on normal tables.

You can use a non-clustered columnstore index to efficiently run real-time operational analytics on the Business Central database without the need to define SIFT indexes up front (and without the locking issues that SIFT indexes sometimes impose on the system.) Whenever you would normally add a SIFT key on fields to do summation/count operations on, use a non-clustered columnstore key to add all the fields to the index instead.

Example

To illustrate, here's a simple example of replacing two SIFT keys with a single non-clustered columnstore index. Suppose you already have implemented two SIFT keys:

• Key1: "WareHouseId, Color" SumField: "OnStock"
• Key2: "WareHouseId, ItemId, Size" SumField: "OnStock"

With a non-clustered columnstore index, you could just have one index defined as:

• ColumnStoreIndex = WareHouseId,Color,ItemId,Size,OnStock

For more information, see:

• ColumnStoreIndex property
• Columnstore indexes: Overview

Clustered and non-clustered keys

A key definition includes the Clustered property that you use to create a clustered index. A clustered index determines the physical order in which records are stored in the table. Based on the key value, records are sorted in ascending order. Using a clustered key can speed up the retrieval of records.

There can be only one clustered index per table. By default the primary is configured as a clustered key.

Sort orders and secondary keys

The following example shows how the primary key influences the sort order when a secondary key is active. The Customer table includes four entries (records), and the records in the Customer table have two fields: Customer Number and Customer Name.

The following table includes the key list for the Customer table.

When you sort by the primary key, the Customer table resembles the following table.

If you select the secondary key for sorting, then the order is based on the contents of the Customer Name field. Because the contents of these fields aren't unique, the records must be subsorted according to the primary key.

How keys affect performance

Searching for specific data is easier if several keys are defined and maintained for the table that holds the wanted data. The indexes for each key provide specific views that enable quick and flexible searches. There are advantages and disadvantages to using many keys, as demonstrated in the following table.

The decision whether to use a few or many keys isn't easy. The appropriate keys and the number of active keys to use is a compromise between maximizing the speed of data retrieval and data updates (operations that insert, delete, or modify data). In general, it may be worthwhile to deactivate complex keys if they're rarely used.

The overall speed depends on the following factors:

• Size of the database.
• Number of active keys.
• Complexity of the keys.
• Number of records in your tables.
• Speed of your computer and its hard disk.

Defining new keys

To define keys, add the keys keyword after the fields definition, and then add a key keyword for each key:

keys
{
    key(Name1; Fields)
    {

    }
    key(Name2; Fields)
    {

    }
}

Replace Name with descriptive text that you want to use to identify the key. Replace Field with the name of a field that you want to use as the key. If you want to include multiple fields in a single key, separate each field with a comma.

In a table object, the first key keyword defines the primary key. Subsequent key keywords define secondary keys.

The following code illustrates simple examples of a table object and table extension object.

table 50120 MyBaseTable
{
    fields
    {
        field(1; MyBaseField1; Integer)
        {
        }
        field(2; MyBaseField2; Integer)
        {
        }
    }

    keys
    {
        key(PK; MyBaseField1) //primary key
        {
            Clustered = true;
        }
        key(Key1; MyBaseField2) //secondary key
        {
        }
    }
}

tableextension 50121 MyBaseTableExt extends MyBaseTable
{
    fields
    {
        field(3; MyExtField1; Integer)
        {
        }
        field(4; MyExtField2; Integer)
        {
        }
        field(5; MyExtField3; Integer)
        {
        }
    }

    keys
    {
        key(ExtKey1; MyExtField1) //secondary key
        {
            IncludeFields = MyExtField2,MyExtField3;
        }
        key(ExtKey2; MyBaseField1, MyBaseField2) //secondary key
        {
        }
        // The following key isn't allowed because it contains fields from the base table and the table extension
        //key(ExtKey3; MyBaseField1, MyExtField2)
        //{
        //}
    }
}

tableextension 50122 MyCustomerKeyExt extends Customer
{
    // This example illustrates how to use a table extension to add a key on the Customer table from the base application
    keys
    {
        key(ExtKey1; "No.", "Name", City) 
        {
        }
    }
}

Key properties

There are several properties that configure the behavior of a key, such as the Enabled, Clustered, and Unique properties:

keys
{
    key(PrimaryKey; ID)
    {
        Clustered = true;
    }
    key(CustomerInfo; Name,Address,City)
    {
        Unique = true;
    }
   key(Currency; Currency Code)
    {    
        Enabled = false;
    }
}

For a more information about the different key properties, see Key Properties.

Limitations and Restrictions

Keys in table extension objects

In table extension objects, you can define multiple keys, just like in a table object. However, the following limitations apply:

• In Business Central 2020 release wave 2 and earlier, keys in table extension objects can only include fields from the table extension object itself.
• In Business Central 2021 release wave 1 and later, keys in table extension objects can include fields from the base table object and table extension object. However, a single key can't include fields from both the base table object and table extension object. In other words, each key must contain fields from either the base table object or the table extension object.
• You can use the same key name in the table extension, unless the key contains fields from the base table object.

Total number of keys

Up to 40 keys can be associated with a table.

Key modifications

When developing a new version of an extension, be aware of the following restrictions to avoid schema synchronization errors that prevent you from publishing the new version:

• Don't delete primary keys.
• Don't add or remove primary key fields, nor change their order.
• Don't change properties of existing primary keys.
• Don't add more unique keys.
• Don't add more clustered keys.
• Don't add keys that are fields of the base table.

Identifying keys in code

APPLIES TO: Business Central 2022 release wave 2 and later

When you invoke IntelliSense for table fields, the primary key members are marked with a (PKx) in the IntelliSense list, where x is a sequential number, which indicates the order of the field in the key. This allows you to identify the table fields that make up the primary key and the sequency of these fields in the key.

See also

Key properties Tables overview
Table object
Table extension object
SystemId field