I’ve found out something interesting about the creation of new fields in transparent tables. When we are creating a new field in a transparent table that already have records if we did not check the “Initial Values” flag the new field is created in the database with the NULL default value for all existing records. The interesting thing is that if we do so it will not be possible to use the new field in a WHERE clause properly. Let’s see an example.
We have a transparent table that is not empty and we add a new field (gender). We go to SE16 and check the number of entries. Total number of entries = 10.
If we try to select all records where the field is blank we have the following result (figure below).
If we try to select all records where the field is NOT blank we have the following result (figure below).
As we can see the number of the entries where the field is blank plus the number of entries where the field is NOT blank is not equal to the total. In fact, both are equal to zero. It happens because of the NULL value. The NULL value is not an initial value but undefined.
It is not possible to see, even in the abap debugger, which records have the NULL value because they are converted to the initial value of the respective data type. We can only see it in the database.
In my humble opinion, it is a good practice to check the initial value flag if we are creating new fields in a table that is already populated. Special attention to append structures where the flag may be hidden. If so, use the menu to enable it in the screen (see figure below).
Here is the result after checking the “Initial Values” flag.