Best Practice of Column/Field Creation (and Deletion)

On a subproduction instance (subprod A), from the Dictionary, in an update set, a field is created.
- Columns should be created on subprods only. And on tables that have a small number of records or are empty.
- Reasons are in section "Transaction Timeout during field creation?"
The update set might be committed/moved onto a different instance for further testing (subprod B).
Eventually, the update set is committed/moved to production (prod C).

Column and field can be interchangeable. In the database, the word "column" is usually used. On the instance, "field" is typically used.

This will depend on how many records the table contains. The larger the table, the longer it will take.
- There is no definite duration we can give. We have seen a column creation on a table that has 10 million records take around 1.5 hours.
The duration comes from the constraints of the database and unfortunately, we cannot do anything about it.
Note that the [task] table is very likely flattened meaning that other tables such as change_request, problem, sc_req_item, etc are all a part of task (these tables extend from task).
- When creating fields on any table which extends [task] table, the entire [task] may have to be altered depending on various factors.
- Example: I am creating a field on [change_request] table which only has 20k records. I expect it to be pretty fast. However, the entire [task] will have to be altered. In this scenario, task has 8 mil records and so the table alter will take a while. Note that the [task] table is very likely flattened meaning that other tables such as change_request, problem, sc_req_item, etc are all a part of task (these tables extend from task).

When creating columns through the Dictionary, that transaction is subject to Quota Rules, specifically, the out-of-box one named: UI Transactions
- Default quota rules
Since the creation will take a while, the out-of-box UI Transaction Quote Rule will terminate any transaction longer than 298 seconds (~5 mins).
If you are creating a column on any instance, the termination of the column creation often cause improper behavior of the column
- Some examples of improper behavior: the field cannot be added to a list view/form, data cannot be stored in the field, the name of the column is "label"
Two workarounds to this:
1. Truncate/clean up the table. Since on subprod instances, millions of records are generally not needed, truncating the table will speed up the process.
2. Disable the UI Transaction Quote Rule. We DO NOT recommend doing this on production. Columns should only be created on subprod instances.
3. As per the workaround in KB0695149 - Long-running transactions will generate sys_update_xml in the Default update set by the guest user if the session expires before completion, you can create a column from the Form/Layout Editor, and this will run in the background and not time out.
Update sets ARE NOT subject to any Quota Rules as they run in the background.

The creation of fields should only be done on subprod instances.
They should also be captured in update sets.
Field/column creations applied via update sets will not time out as they are not subject to Quota Rules. They run in the background.

Online alter operations are completed in the following steps:

An empty table is created with the same schema as the table that is to be altered.
The ALTER operation is performed on the new, empty table.
Triggers are added to the live table to copy any changes that are made against the live table to the new table.
All data is copied in chunks, from the live table to the new table.
Once all of the data is copied, the tables are swapped. This requires a very brief table lock.
The old live table is dropped.

This method will only briefly lock the table, and should not cause any noticeable issues on the instance for end users.

On out-of-box instance, they will fail due to a system property: KB0714738 - Adding Columns to a Large Table
If this occurs, please contact Support.