Numeric Precision
Salesforce displays a Currency, Number, or Percent field with a fixed number of decimal
places (its Setup "Length" and "Decimal Places"), but that display precision is not what
Salesforce actually stores or returns from the API. A field declared with 0 decimal
places can come back as 123.456789012345678, and a Salesforce Number(18, 0) field can hold
more significant digits than an ordinary SQL Server decimal column sized to match the
declared shape can represent without rounding.
Speediful controls how much of that value survives into the destination SQL Server
column with one install-wide setting, SLAM_Settings.default_numeric_precision. There
is no per-call override. Every SLAM_query*, SLAM_query_by_id, SLAM_replicate, and
SLAM_table_template operation uses the same setting.
The three modes
high (the default)
The SQL Server column is widened to decimal(38, N): the Salesforce field's integer digits,
plus as many decimal places as SQL Server's 38-digit maximum leaves. For a Salesforce field
declared (18, 0), such as Account.AnnualRevenue, the SQL Server column is decimal(38, 20).
A value with more than N decimal places is rounded at the Nth place, and a value too
small for that scale rounds to 0 (underflow). For a SQL Server
decimal(38, 20) column, that is any value whose magnitude is below 5E-21. If a field can carry such
values, use text mode.
No action is needed to get this. It is the setting on a fresh install, and the
setting SLAM_set_numeric_precision falls back to if it can't be read at all. Data
accuracy outweighs the wider column.
display
display mode can lead to significant loss of precision. Salesforce stores more decimal
places than a field's "Decimal Places" setting shows. display rounds every value to that
setting, so a Salesforce Number(18, 0) field holding 123.456789 arrives in SQL Server as 123.
The SQL Server column matches the Salesforce field's declared Setup precision and scale
exactly: decimal(18, 0) for that same Account.AnnualRevenue field. Choose this only if you need
column widths to stay exactly what Salesforce Setup declares (for example, to match an
existing downstream schema, view, or ETL mapping that assumes the declared shape) and the
rounding is acceptable.
Values are rounded to fit into the SQL Server data type. A very small value may round to zero due to underflow.
text
The destination column is nvarchar(50) and holds the value as text, exactly as
Salesforce returned it. This includes magnitudes that neither high nor display mode
can represent as a fixed-scale decimal at all (Salesforce's numeric range extends well
beyond what any decimal(38, N) column can hold).
The column holds a mix of positional and scientific notation. An ordinary value like
123.45 is stored as 123.45. A very small or very large value is stored in scientific
notation (5.123E-25) so that it always fits the 50-character column width. A plain
CAST to decimal therefore fails with a conversion error on any row stored in scientific
notation.
Use text mode for fields where the exact value matters more than having a native
numeric column to query directly, for example archival or audit copies, or a field known
to carry values high mode would silently round.
Underflow
In high and display mode, a value too small for the SQL column scale rounds to 0.
This is ordinary decimal rounding, not a bug.
| Mode | Salesforce field | SQL Server column | Values that round to 0 |
|---|---|---|---|
high | Number(18, 0) | decimal(38, 20) | Between -5E-21 and 5E-21 |
display | 2 decimal places | 2 decimal places | Between -0.005 and 0.005 |
In high mode, a Salesforce field with fewer integer digits gets more decimal places, so its
cutoff is smaller. In display mode, the cutoff is half the field's last decimal place.
Speediful counts these and reports them at the end of the run:
WARNING: Account.Minuscule_Multiplier_Weight__c: 5 of 1,000 values were too small for the column and were stored as 0. Use text numeric precision to preserve them.
If you see this message and the rounding-to-zero matters for that field, switch that
load to text mode, which preserves the value exactly (as text) regardless of
magnitude.
Underflow is reported against the schema Speediful would create for a fresh table. On an
append into a pre-existing table with a narrower scale than Speediful would otherwise
choose, the count is measured against the wider schema, not the destination table's
actual (narrower) column, so it can under-report for that specific case. If this
matters for your table, confirm the actual column scale directly
(INFORMATION_SCHEMA.COLUMNS) rather than relying on the count alone.
Changing the setting
EXEC dbo.SLAM_set_numeric_precision 'display'
Valid values are 'high', 'display', and 'text' (case-insensitive). There is no
per-call @numeric_precision parameter on any procedure. See
Speediful Configuration Tables
for where this fits alongside the product's other settings.