Disable Indexes

In this article, you will learn how to disable and restore table indexes for a table in the Aident SQL Migration app in Business Central.

What's the purpose of the function?

For tables with many records (e.g. entry tables), migrating can take a very long time if other keys are enabled in addition to the primary key. The reason for this is that after each inserted record, all enabled indexes are updated. To increase speed during migration, all additional indexes for a destination table can be disabled before migration. Only after the records have been migrated should the previously disabled indexes be rebuilt.

Limitations

Disabling and rebuilding the table indexes only works if the destination database matches the database of the current server instance. Running the queries on different destination databases isn't supported.

Set Flag Disable Indexes in Table Mapping

Follow these steps to disable and enable table indexes:

Navigate to Database Assignments
- In Business Central, search for and go to the Database Assignments page.
Open Database Assignment
- Open the database assignment for which the table indexes should be disabled. The Database Assignment Card page opens.
Open Table Assignment
- Open the table assignment for which the table indexes should be disabled. The Table Assignment Card page opens.
Set Flag Disable Indexes
- Set the Disable Indexes flag to true in the Settings FastTab.

Disable and Rebuild Indexes

Navigate to Database Assignments
- In Business Central, search for and go to the Database Assignments page.
Open Database Assignment
- Open the database assignment for which the table indexes should be disabled. The Database Assignment Card page opens.
View Indexes
- Click View Indexes in the Indexes action menu of the Database Assignment Card page. The Table Indexes page opens.
Update index information
- Click Update Index Information in the action bar. The index information for all table assignments is now updated with the Disable Indexes field enabled. Since there is a table in the SQL database for cross-tenant tables, a row is displayed for each possible destination company (according to the company assignment).
- The No. of Indexes field displays the total number of secondary indexes.
- The No. of Disabled Indexes field displays the number of disabled secondary indexes.
- The No. of Different Index Status field displays the number of indexes that have a status different from the original status.
Disable and Rebuild Indexes
- Disable All Indexes:
  - Click Disable All Indexes for Selected Tables in the Disable Indexes action menu to disable all indexes for the selected tables.
  - Click Disable All Indexes in the Disable Indexes action menu to disable all indexes for all tables.
- Rebuild Disabled Indexes:
  - Click Rebuild Disabled Indexes for Selected Tables in the Rebuild Disabled Indexes action menu to rebuild the disabled indexes for the selected tables.
  - Click Rebuild Disabled Indexes in the Rebuild Disabled Indexes action menu to rebuild the disabled indexes for all tables.
- Rebuild All Indexes:
  - Click Rebuild All Indexes for Selected Tables in the Rebuild All Indexes action menu to rebuild all disabled indexes for the selected tables.
  - Click Rebuild All Indexes in the Rebuild All Indexes action menu to rebuild all disabled indexes for all tables.
Disable and rebuild single indexes
- Click View Indexes in the Home action menu. The Table Index Lines page opens and the current status of the indexes is displayed.
- Click Disable Index to disable the selected index. The Is Disabled Current flag is set to true. Depending on whether the index was disabled in the original state, the field Different Status is set to true or false.
- Click Rebuild Index to rebuild the selected index. The Is Disabled Current flag is set to false. Depending on whether the index was disabled in the original state, the field Different Status is set to true or false.

Next step