How many types of tables available in Dynamics 365 Business Central?

Table types in Business Central D365

We can distinguish four types of tables with a different technical implementation:

• Database tables
• System tables
• Virtual tables
• Temporary tables

Database tables

Most tables in our database are database tables, what we see as normal tables. You will have read and write rights on database tables. Examples are the customer table, vendor table and item table.

System tables

System tables are a special kind of table, because these tables and their data are necessary for the system to function. They are saved in the database and they will have object numbers above two million. Examples are the company table, the profile table and the permission table.

Virtual tables

You can find the definition of a virtual table in the database, but the data is created at runtime, so it’s not stored in the database. You as a developer will not have write rights to virtual tables. The active session table is an example.

Temporary tables

A temporary table only exists in memory of a client. It keeps an image or a copy of another database table. It has no data and it’s frequently used in posting routines and document reports.

Primary and Secondary Keys

Each table in Microsoft Dynamics 365 Business Central needs a primary key. You cannot have keyless tables in your application. If you don’t create a key yourself, the application will take the first field of your table description as your primary key.

In a table you can have primary and secondary keys. The primary key is always the first key in the list. It is always active, and it makes records unique. You can have up to 40 keys per table, and each key can consist of maximum 20 fields.

Secondary keys are optional, and they can help you perform search actions faster. They might have an impact on performance, as they are indexes in SQL Server. So, a best practice is have no more than three to five secondary keys maximum per table.

How to define primary key in AL via Visual Studio Code?

table 50100 Stocks
{
fields
{
field(1;"No.";Code[20])
{

}
}
keys
{
key(PrimaryKey;"No.")
{
Clustered=TRUE;
}
}
}