TableType Property = Temporary

If you are a C/AL or AL developer I’m sure you have already used temporary records to store and process runtime data. I have been using it heavily and must say I liked it very much. Before version 17 of Business Central, developers had to declare a record as a variable and then mark that defined variable as temporary. The process was OK and working fine as long as the fields are sufficient enough to store the runtime values. As an example, if the developer wants to store a decimal value and the temporary record does not have a decimal field then the developer has to face some tricky situation. One of the options developers has is adding a new field to a physical table and proceeding with the development, but it is a bit costly to add a field to a physical table just to hold a value for a runtime operation.

This is where the new TableType property comes to play. Developers can now define a schema that will only run in the memory and not create a physical table. This means at a later stage if the developer decided to do a schema change they can do it without worrying about the destructive schema changes.

Can we change the existing table to a temporary table?

You can change an existing table from Normal to Temporary. The table will be deleted from the database when you synchronize the extension and if the table contains data, you’ll have to use the ForceSync mode.

What are the advantages of using temporary tables?

Since the temporary table only runs in the memory of the Business Central Server, it reduces the load on both the network and the SQL database server which will result in better performances for the customer. Another advantage is that since the table is not getting created physically on a SQL server, at any given point of time the developer can change the schema of the table without worrying about the destructive schema changes.

How to define a Temporary table?

You can use the TableType property to define a Temporary table. Check the below example.

table 50000 MyTable
{
    DataClassification = ToBeClassified;
    TableType = Temporary;

    fields
    
    ...
}

This implementation has the same effect as using a temporary record variable or setting the SourceTableTemporary property on a page. But the advantage is that the table schema isn’t synchronized with the database. 

Happy coding!

Thank you and Regards,
Tharanga Chandrasekara