Exploring Business Central Custom Table Indexes

by Steve Endow

In my last post, I poked around in SQL to see what custom fields looked like in the database for a Business Central table extension.

While looking into fields, I happened to notice the table index that is automatically created for the table extension.

Automatic Table Extension Index

If we look at this index, we see it is a Clustered Unique index for the "No_" field only.

Default Clustered Index on Table Extension

Why is this default index created automatically?  This Table Keys docs page briefly mentions the default Clustered Index on table extensions.

Table Extensions Inherit the Primary Key of their Base Table

If we look at the Customer base table, we see the matching primary key, which is also for the "No_" field.

Base Table Primary Key

So this makes sense.  The Table Extension uses the same primary key, facilitating a JOIN and indexed lookups against the table extension.

The Table Keys doc page reminds us there can only be one Primary Key and one Clustered Index on a table, but we can add secondary keys--additional indexes--on the database table that is created for our BC table extension.

This can be done in AL using the "keys" keyword.

Create Keys in AL Table Extension

You can create multiple keys using the "key" keyword.  Each key has a name, and a comma delimited list of fields that will be part of the table index.

Defining Table Keys in AL

My example is for a Table Extension, which inherits a primary key from its base table.  But note that if you create an independent custom table, the first key you define will be the primary key.

So in my example above, I defined a secondary key creatively called "testkey".  I added TestDate and TestInteger as fields to that new index.

Table Extension Secondary Key: Unique Non-Clustered Index

The AL code will automatically create a non-clustered SQL index on the extension table.

Wait a minute--that's a Unique index.  I can imagine many scenarios where data stored in a table extension will not be unique for all records.  So what if I want a non-unique index?

I looked at the documentation for the Business Central Unique index property, and found this note:

"The Unique property isn't supported in table extension objects"

This says that the Unique property isn't supported in table extensions--and the default value for Unique is True.  So any indexes you add to a table extension will be Unique.  Period.

But wait--what if I have a table extension to store a "Customer Region" field--and I have dozens of customers with a region value of "West".  If I want to add an index to my Customer table extension that includes Customer Region, it needs to allow duplicate values.

So how can the additional indexes all be unique?  This didn't make any sense.

So I tried entering a few test records to see what happened.

Same Table Extension field values in two different records

I entered the same values in a few table extension fields on two different records.  I waited for an error.  But nothing happened.  I entered the values in the fields, and the values were saved.

Huh.

I opened the index details and found the answer.  (Todd Scott on Twitter also explained this just as I found the answer)

The primary key field is added to the additional indexes

When the new index is created, the primary key from the 'base' table is included in the index.  So even if I have the same value for TestDate and TestInteger, having the No_ field in the index makes each entry unique.

Included Columns

While doing this research on Business Central indexes, I wondered about Included Columns.  This is a very cool feature of SQL Server indexes that allows you to include some additional fields in your index to reduce separate lookups outside of the index.

If you are familiar with a "covering index", the value of Included Columns should be pretty obvious.  It offer the benefits of a "wider" index with less index overhead--since the included columns are not "keys" in the index.

Support for Included Columns was added to Business Central in October 2021 through the use of the IncludedFields keyword.

IncludedFields property = SQL Index Included Columns

If you are creating custom tables or table extensions in Business Central, I highly recommend understanding SQL Server indexes and basics of index optimization.

SQL Server performance optimization and index optimization is an entire career, so I don't expect any ERP developer to be an expert, but you should at least understand the theory of SQL indexes, how they work, and how to generally improve query performance using indexes.

Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics 365 Business Central and related technologies.

You can also find him on Twitter and YouTube, or through these links:  links.steveendow.com