Skip to main content

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​

warning

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.

ModeSalesforce fieldSQL Server columnValues that round to 0
highNumber(18, 0)decimal(38, 20)Between -5E-21 and 5E-21
display2 decimal places2 decimal placesBetween -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.

note

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.