XLC Investigates - When ALTER TABLE Moves the Decimal Point in SAP HANA

The arithmetic explains the symptom. A fixed-point decimal can be thought of as an integer with an implied decimal point. At scale 6, 219 corresponds to the integer 219000000. To preserve 219 at scale 12, the integer must grow by a factor of one million. If the same integer is interpreted at scale 12, the value becomes 0.000219. In the failing tests below, the observed divisor was exactly 10^(target scale − source scale).

Here is the DDL at the center of one test. I ran it against a populated column table whose UNITS column started as DECIMAL(22,6), after committing the inserts and merging the delta.

What changed at precision 19 and 29?

At XLC, we wrote a SQLScript test procedure specifically for this investigation. I ran RUN_DECIMAL_SCALE_TESTS(0) to test each transition against a fresh table. The procedure compared the exact values and their frequencies before and after the DDL. A row count alone would have missed every scaling error in the run.

What if only the precision changes? Widening DECIMAL(5,3) to DECIMAL(8,3) would increase the number of available integer digits from two to five while keeping three fractional digits. A stored value such as 9.291 would still be 9.291. In the test run, I used DECIMAL(5,3) → DECIMAL(7,3) as a precision-only control, and all 1,006 values remained unchanged. With DECIMAL(5,3) → DECIMAL(7,4), the same target precision and an increase in scale from 3 to 4 produced values one-tenth of their original size.

For DECIMAL(12,6), raising the target precision from 18 to 19 changed the outcome with the target scale held at 8. For DECIMAL(22,6), the same happened from 28 to 29 with the target scale held at 12. The boundaries line up with the capacity of signed fixed-width integers.

A signed 8-byte integer can hold every 18-digit decimal integer and only part of the 19-digit range. The same relationship holds for 28 and 29 digits at 12 bytes. That gives us a hypothesis: crossing one of these boundaries changes the internal representation and takes a conversion path that preserves the value. The tests establish where the behavior changed in this matrix. The exact format and conversion path taken by each statement remain unconfirmed.

Compression changed the outcome in the lab

I ran DECIMAL(22,6) → DECIMAL(28,12) on two loaded column tables with the same 11,005 rows. Each contained 10,000 copies of 42. HANA chose SPARSE for one table. For the other, I disabled automatic compression optimization before inserting the data and verified that the loaded column reported DEFAULT immediately before the ALTER TABLE.

The column’s compression state at the time of the change matters for an investigation. When checking a test column in M_CS_COLUMNS, inspect LOADED alongside COMPRESSION_TYPE. HANA can also report DEFAULT for an unloaded column. SAP Help on checking column compression

What did the workaround cost in one timing test?

SAP’s workaround for the applicable HANA 2 SPS08 revisions disables the direct import optimization via the internal parameter direct_import_with_up. The note warns that this can affect ALTER TABLE performance. I timed the same change, DECIMAL(22,6) → DECIMAL(23,12), with the optimization disabled and active. I rebuilt the table from scratch between runs. The loaded column reported DEFAULT and contained 100,001,005 rows, including 100 million copies of 42. SAP Note 3849542

Execution took about 59 times as long with the optimization disabled in this pair of runs. This was a timing comparison on one deliberately skewed dataset, with one measurement per setting. How the workaround affects other tables and upgrade runtimes remains open.

Our working hypothesis is that the slowdown is limited to ALTER TABLE operations that change a DECIMAL column and would otherwise use the direct import optimization. In my tests, I observed no slowdown for delta merges, compression optimization, or partitioning.

How to assess a system and brief management

Start with the HANA revision and the effective configuration when the DDL ran. Then build a list of candidate column changes from the HANA audit log, any available executed_statement trace, and the SUM phase logs. For each candidate, recover the physical HANA column definition before and after the change. Look for a scale change and establish the column’s compression state at that time where historical evidence is available.

For ABAP-managed tables, compare DD02L (table metadata) and DD03L (field metadata) with a pre-change copy, if available. This helps identify changes in the ABAP Dictionary. Verify the actual source and target DECIMAL definitions against historical HANA metadata or DDL before deciding whether a column change is in scope.

Once the physical column definitions are known, you can narrow the candidate list:

• DECIMAL(14,3) → DECIMAL(15,3) can be excluded for this issue because the scale remains 3.
• DECIMAL(5,3) → DECIMAL(19,5) crosses the precision-19 boundary. Under our proposed mapping, this would switch from FIXED8 to FIXED12, putting the change outside the scenario described in the note if that representation change is verified. This exact transition was outside the published test matrix.

Apply these exclusions to individual column changes. A table can be removed from the candidate list once every relevant column change has been ruled out.



Details: FIXED8

A FIXED8 is sent as an 8-byte signed integer in little-endian format.

The field has a length of 9 bytes if the value is not NULL and 1 byte if the value is NULL. 



Details: FIXED12

A FIXED12 is sent as a 12 byte signed integer in little-endian format.

The field has a length of 13 bytes if the value is not NULL and 1 byte if the value is NULL.     

In my lab, the audit record showed SUCCESSFUL and included the target DECIMAL(28,12) definition. I knew the source definition from the setup and had measured compression separately. The before-and-after data comparison established that the values had changed. On a customer system, a pre-change backup restored to a comparison system or a trusted historical extract can provide that baseline. Account for legitimate business updates between the two snapshots when comparing values.

The management question is usually straightforward: Can we say with certainty whether this happened to our data? Without a reliable comparison point, the team can identify candidate changes and investigate suspicious values, but cannot conclusively confirm or rule out historical changes across the affected data. Report which changes you found, which columns you verified against a baseline, and where the evidence is still missing. If the comparison confirms affected rows, use the correction guidance in the note and establish the affected row set before applying any update. SAP Note 3849542