How SQL Server Stores MONEY and SMALLMONEY, and the Rounding Trap
The money type looks like the obvious choice for a column that holds money. It even prints with a currency sign. But Microsoft’s own documentation carries a warning most people never read: avoid it for values used in calculations, because you can lose accuracy to truncation.[1] To see why, it helps to know exactly what money is on disk, which turns out to be simpler and more revealing than the name suggests. This post takes money and smallmoney apart on the page, explains their oddly specific ranges, and shows the rounding trap with numbers.
This continues the byte-level storage series and is a direct sequel to how SQL Server stores decimal and numeric, which closed by recommending decimal over float for money. It also sits alongside strings and UTF-8 and the uniqueidentifier. If you have not watched a row get pulled apart with DBCC PAGE, this older post covers the mechanics. Every byte below is from a SQL Server 2019 instance, and the scripts are included so you can reproduce them. Randolph West wrote his own excellent post on how SQL Server stores money over at bornsql.ca, and it is well worth reading alongside this one.
It is just a scaled integer
Strip away the currency sign and money is an 8-byte integer, and smallmoney is a 4-byte integer. The value you see is that integer divided by 10,000. There is no precision or scale to declare, because both are fixed: four digits after the decimal point, always.
You can confirm the sizes from the catalog:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE #sizes ( [m] money NOT NULL , [sm] smallmoney NOT NULL ); SELECT [column_name] = [c].[name] , [max_length] = [c].[max_length] , [precision] = [c].[precision] , [scale] = [c].[scale] FROM [tempdb].[sys].[columns] AS [c] WHERE [c].[object_id] = OBJECT_ID(N'tempdb..#sizes', N'U') ORDER BY [c].[column_id]; |
|
1 2 3 4 |
column_name max_length precision scale ----------- ---------- --------- ----- m 8 19 4 sm 4 10 4 |
The “scale 4” is the tell. Both types keep exactly four decimal places, which is where the divide-by-10,000 comes from.
Why the range is so oddly specific
The documented range of money is -922,337,203,685,477.5808 to 922,337,203,685,477.5807, and smallmoney runs -214,748.3648 to 214,748.3647.[1] Those numbers look arbitrary until you multiply them by 10,000:
|
1 2 3 4 5 |
SELECT [money_max_x10000] = CONVERT(decimal(19,0), 922337203685477.5807 * 10000) , [bigint_max] = CONVERT(bigint, 9223372036854775807) , [smallmoney_max_x10000] = CONVERT(int, 214748.3647 * 10000) , [int_max] = CONVERT(int, 2147483647); |
|
1 2 3 |
money_max_x10000 bigint_max smallmoney_max_x10000 int_max ------------------- ------------------- --------------------- ---------- 9223372036854775807 9223372036854775807 2147483647 2147483647 |
money‘s maximum is exactly bigint‘s maximum (2^63 – 1) divided by 10,000, and smallmoney‘s is exactly int‘s maximum (2^31 – 1) divided by 10,000. The range is not a design choice about currency at all; it is just what an 8-byte or 4-byte signed integer can hold once you reserve the last four digits for the fraction.
The on-disk bytes
Time to prove the storage claim on a real page. Three rows, each with a money and a smallmoney column:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE [dbo].[money_demo] ( [label] varchar(20) NOT NULL , [m] money NOT NULL , [sm] smallmoney NOT NULL ); INSERT INTO [dbo].[money_demo] ( [label], [m], [sm] ) VALUES ( N'1.00', 1.00, 1.00 ) , ( N'1234.5678', 1234.5678, 1234.5678 ) , ( N'-1.00', -1.00, -1.00 ); |
Reading the fixed-length columns off the page with DBCC PAGE, here is what each value occupies:
|
1 2 3 4 5 |
value money (8 bytes) smallmoney (4 bytes) integer --------- ------------------------ -------------------- ----------- 1.00 10 27 00 00 00 00 00 00 10 27 00 00 10000 1234.5678 4e 61 bc 00 00 00 00 00 4e 61 bc 00 12345678 -1.00 f0 d8 ff ff ff ff ff ff f0 d8 ff ff -10000 |
Take 1234.5678. Multiply by 10,000 and you get the integer 12,345,678, which is 0x00BC614E. SQL Server writes it little-endian (least significant byte first), so on the page it reads 4e 61 bc 00, and the 8-byte money version is the same value zero-extended: 4e 61 bc 00 00 00 00 00. No fancy encoding, no separate sign byte, no word-swapping (any byte-order quirk you may have heard about lives in client protocols, not in the page). It is a plain two’s-complement integer, which is why -1.00 shows up as f0 d8 ff ff ...: that is -10,000 in two’s complement.
That is the whole storage story. money is a bigint wearing a decimal point, and smallmoney is an int wearing one.
The rounding trap
Fixed storage is efficient, but the fixed four-decimal scale is also the source of the warning in the docs. Because every intermediate result is forced back to four decimal places, calculations that divide and then multiply can drift. Watch money fail to round-trip a simple (10 / 3) * 3:
|
1 2 3 4 5 |
SELECT [money_third] = CONVERT(money, 10) / 3 , [money_result] = CONVERT(money, 10) / 3 * 3 , [decimal_third] = CONVERT(decimal(19,10), 10) / 3 , [decimal_result] = CONVERT(decimal(19,10), 10) / 3 * 3; |
|
1 2 3 |
money_third money_result decimal_third decimal_result ----------- ------------ -------------- -------------- 3.3333 9.9999 3.333333333333 9.999999999999 |
Both types round a non-terminating result, but money truncates it at the fourth decimal, so its error is about 0.0001. The decimal(19,10) carries far more fractional precision (SQL Server even widens the scale of a division result), so its error sits down around 0.000000000001, roughly a hundred million times smaller. In a single expression that is a rounding curiosity. Run it across millions of line items in a SUM of computed values, taxes, currency conversions, apportioned discounts, and those 0.0001 errors accumulate into a discrepancy an auditor will find. That is precisely the scenario the documentation is warning about.
So should you use money?
It is not a type to fear, but it is a type to place deliberately:
- For values you store and display, money is fine. A stored balance, a logged transaction amount, a price you read back and show: the four-decimal scale is plenty, and 8 bytes is compact.
- For values you compute with, prefer decimal. Anything that goes through division, multiplication by rates, or long chains of aggregation wants the extra scale. Follow the documentation and use
decimalwith at least four decimal places, and usually more. The decimal storage is only a few bytes larger and you control the precision. - Never reach for float for currency. Both
moneyanddecimalare exact scaled integers underneath;floatis a binary approximation that cannot represent most cents exactly. Truncation you can reason about; binary rounding error you cannot. - smallmoney is rarely worth it. Saving 4 bytes caps you at about 214,000 units. Unless the row count is enormous and the values are guaranteed small, the risk of hitting the ceiling outweighs the saving.
The name money promises more than the type delivers: it is not a currency-aware type, it stores no currency code, and its four fixed decimals are a storage decision, not an accounting one. Once you see it as a scaled bigint, both its efficiency and its rounding trap make complete sense.
Have you been bitten by money truncation in a report that did not foot, or do you reach for decimal by reflex? I would like to hear it. Find me on Bluesky or LinkedIn.
References
- money and smallmoney (Transact-SQL) – Microsoft Learn. The 8-byte and 4-byte sizes, the documented ranges, and the warning about rounding errors through truncation in calculations. ↩