smallmoney: Four Fractional Digits Need a Contract

smallmoney has a fixed range and four fractional digits, rather than an application’s complete amount contract. I keep the original decimal beside its conversion to see what information changes.

A type name does not identify a currency

smallmoney is a four-byte type with four fractional digits. Its range is approximately negative 214,748 to positive 214,748 monetary units. The exact endpoints differ in their final digit. This example deliberately stays far inside that range.

The type stores a numeric amount without retaining a currency identifier. A symbol accepted during input doesn’t add a currency column. A multi-currency record therefore needs that information separately. The numeric type cannot decide which unit an application intended.

I’d define the allowed unit, range and scale before choosing storage. The name smallmoney isn’t a validation policy. A value being representable doesn’t establish that its currency or business meaning is correct. This article examines only a bounded conversion behavior.

Compare the original scale with the converted scale

The script supplies decimal(9,5) values with five fractional digits. They include values just below and above a selected four-digit rounding boundary. A negative example, zero and NULL remain in the list. Every original input stays visible.

Each input is converted to smallmoney. A second display column converts that result to decimal(12,4). The display column fixes an easy comparison format. It doesn’t restore the fifth digit lost during the earlier conversion.

The selected 1.23454 input is expected to become 1.2345. The 1.23456 input becomes 1.2346. The negative input is compared separately. These examples avoid a halfway value and don’t claim every conversion or arithmetic expression uses an identical rounding contract.

WITH Inputs AS
(
    SELECT Id, CAST(InputValue AS decimal(9,5)) AS InputValue
    FROM (VALUES (1, 1.23454), (2, 1.23456), (3, -1.23456),
                 (4, 12.34000), (5, 0.00000), (6, NULL)) AS v(Id, InputValue)
)
SELECT i.Id, i.InputValue, a.SmallAmount,
       CAST(a.SmallAmount AS decimal(12,4)) AS DisplayAmount
FROM Inputs AS i
CROSS APPLY (VALUES (CAST(i.InputValue AS smallmoney))) AS a(SmallAmount)
ORDER BY i.Id;

Choose an arithmetic contract separately

Money types can cause truncation and rounding problems in calculations. A suitable decimal type is the usual alternative for such requirements. This demonstration doesn’t promote smallmoney as a universal accounting type. It makes a type conversion visible.

I can justify retaining an existing smallmoney field while investigating a system’s current behavior. Changing storage immediately can introduce compatibility work beyond the original question. First identify the input and calculation contract. A conversion example isn’t an entire migration plan.

A final cast to a wider decimal type cannot recover discarded input digits. The original decimal column is therefore retained in the output. If those extra digits matter, the destination contract needs review. A more attractive formatted value doesn’t change that loss.

Keep zero and missingness distinct

Zero is a real input with a four-digit zero output. NULL remains missing through both conversions. The script doesn’t substitute a default currency amount. That difference should remain visible when reviewing any later presentation rule.

The CTE reads six literal inputs and the SELECT performs explicit conversions. It creates no objects or session settings. ORDER BY fixes the case sequence. Keep every original input, native smallmoney value and displayed decimal result together.

Compare all changed digits and SQL types across the complete input set. Keep the original amount beside its converted value. Retain the negative and missing cases during the check. A positive example alone doesn’t establish the complete stated conversion contract.

Write the contract down first, and the type name matters a lot less.

A monetary type name is not a complete amount policy, it is a bounded numeric storage contract.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.