Dynamics 365 Business Central: the problem of large data deletion – part 2: AlterKey

Yesterday I talked about the always more increasing need to perform large data deletion in SaaS for some tables and I re-launched again a personal desire: having a TRUNCATE option available for some tables (and under some limitations) on SaaS.

In this post I mentioned the Database.AlterKey method, actually not usable on a SaaS environment, and I had offline some discussions with some of you about the topic (happy that many of you agree on the problem). Yesterday evening talking with Marije Brummel – de Krosse turned me the light: hey, I remember I wrote about a post time ago about the Database.AlterKey method and its limited scope… and here it is.

I wrote that post on February 2024 to explain how the newly launched Database.AlterKey method works and on this post I also mentioned the need for a TRUNCATE operation.

The Database.AlterKey(KeyRef, Boolean) AL method alters a table’s key in SQL, either disabling or enabling it. Any alteration only pertains to the current transaction and will be reverted at the end of the current transaction. Any alteration will fail if it’s called on System or non-SQL based tables. Disabling clustered or unique keys is also not supported and will fail at runtime.

Microsoft has a module in the System Application called Table Key. This module contains codeunit 9557 “Table Key” that provides functionality for disabling and re-enabling table indexes:

• procedure DisableAll(TableNo: Integer): Boolean : Disables all keys on the provided table. The method returns true if the keys were disabled successfully, false otherwise.
  • System tables and non-sql based tables are not supported for this operation.
  • Clustered keys, unique keys, SIFT keys, Nonclustered Columnstore Indexes are not affected by this operation.
  • The keys are automatically re-enabled when a Commit() is called, or at the end of AL code execution.
• procedure EnableAll(TableNo: Integer): Boolean : Re-enables all keys that have been disabled on the provided table. The method returns true, if the keys were re-enabled successfully, false otherwise.
  • This method can be used when keys need to be re-enabled (for example, for searching) before a Commit() is called.

Disabling table keys for a massive data deletion can help on improving the performance of the process a lot! If you need to delete a large table in a Business Central on-premises database, you can create a temporary PTE with “target”: “OnPrem” and then execute something like the following:

This will work like a charm and it speeds up your deletion process…

And what about SaaS?

The problem of the Table Key module of the System Application are those two attributes:

After more than 1 year, the Table Key module has still Scope = OnPrem, so it cannot be used in a Business Central SaaS environment.

This is the main point of this PART 2 post about massive data deletion on SaaS. If providing a TRUNCATE option is not an immediate solution, please review the Table Key module of the System Application and:

1. Remove Scope = OnPrem from the above two methods
2. Add (as a PLUS) a new method in the Table Key codeunit that works with a specific key. Actually, the two available methods permits you only to disable and enable ALL the table keys and not a specific key.

Regarding point 2, a procedure to enable/disable a specific key can be easily created as follows:

I think that opening these methods to the cloud environment is something that can be done right now, because they really works and probably they are yet used internally from Microsoft when creating sandboxes.

Hoping that this could be a STEP 1 in order to improve the massive data deletion problem…

Original Post https://demiliani.com/2025/03/26/dynamics-365-business-central-the-problem-of-large-data-deletion-part-2-alterkey/