In this post we look at how SQL Server stores currency values using the `MONEY`

and `SMALLMONEY`

data types. If you’d like to read the previous posts in this series of how SQL Server stores data types, here’s what we’ve got so far:

### 🎶 It’s a rich man’s world 🎶

SQL Server provides two dedicated data types for storing monetary values. Like `BIGINT`

and `INT`

which we looked at previously, you can think of `MONEY`

and `SMALLMONEY`

in a similar way because they require eight bytes and four bytes of storage respectively.

Some time ago I described `MONEY`

as a “convenience” data type which is effectively the same as `DECIMAL(19,4)`

, while `SMALLMONEY`

is effectively the same as `DECIMAL(10,4)`

, but what does this mean?

As I’ve mentioned previously about SQL Server data types, the value before the comma is the total width including the decimal places, while the value after the comma is the number of decimal places only. `DECIMAL(19,4)`

and `MONEY`

both store a value up to 15 digits before the decimal point and four after it, whereas `DECIMAL(10,4)`

and `SMALLMONEY`

can only store a maximum of six digits before the decimal point. Perhaps we might use `SMALLMONEY`

for storing product prices, but we should not consider it for salaries or other financial values where the totals may exceed 10 million without casting it as a different data type first.

#### A note on precision and calculations

One of the biggest complaints people have about the `MONEY`

and `SMALLMONEY`

data types is that they only support a precision of four decimal places. When you start multiplying or dividing these amounts, the precision loss causes rounding to occur.

To that I ask “So what?” The same issue can happen with any fixed-precision data type, and if you want to perform calculations on these values you need to take the precision into account by casting to a value that handles more decimal places. Ultimately it is the business that decides how to handle the loss of precision and rounding that occurs, and is not the fault of the data type. When you design your database, you should always pick the best data type for **storing** the data.

### How are these values stored?

While `MONEY`

and `DECIMAL(19,4)`

are functionally the same, they are stored differently on disk, and this is where it gets interesting. Let’s use a random amount of $ 4,513.19. Since it’s small enough to fit in both `MONEY`

and `SMALLMONEY`

, we can do a simple experiment. When we ask SQL Server to store this value in a `MONEY`

data type, it will store it (byte-reversed) as `0x7CA8B00200000000`

. The `SMALLMONEY`

version of this amount would look almost identical, stored as `0x7CA8B002`

(without the leading zeroes). A quick look at this byte-reversed value (`0x02B0A87C`

) in a hex calculator gives us the amount of 45,131,900. After moving the decimal point four places to the left, we get our starting value of 4513.1900.

Check this out, though:

Value |
Data Type |
Binary (on disk) |

4513.19 | `MONEY` |
`0x` |

4513.19 | `SMALLMONEY` |
`0x` |

4513.19 | `DECIMAL(19,4)` |
`0x01` |

4513.19 | `DECIMAL(10,4)` |
`0x01` |

45131900 | `BIGINT` |
`0x` |

45131900 | `INT` |
`0x` |

We know that `MONEY`

and `SMALLMONEY`

are fixed-precision values with an assumed four decimal places, and that it can store the same values as `DECIMAL(19,4)`

, but when we look at the on-disk values in each of these data types, instead of being the same as `DECIMAL`

we see that `MONEY`

is secretly an integer!

This is even more noticeable when looking at negative values. As we learned in the previous post, integers like `INT`

and `BIGINT`

use two’s complement to store negative values, while `DECIMAL`

uses the last byte (remember, this is all byte-reversed) to store the sign, but otherwise the binary representation of the amount doesn’t change.

Value |
Data Type |
Binary (on disk) |

-4513.19 | `MONEY` |
`0x` |

-4513.19 | `SMALLMONEY` |
`0x` |

-4513.19 | `DECIMAL(19,4)` |
`0x00` |

-4513.19 | `DECIMAL(10,4)` |
`0x00` |

-45131900 | `BIGINT` |
`0x` |

-45131900 | `INT` |
`0x` |

To summarize, the `MONEY`

and `SMALLMONEY`

data types are identical to the integer data types, but can store values with the same range as `DECIMAL(19,4)`

and `DECIMAL(10,4)`

respectively.

Share your thoughts in the comments.

*Photo by Ibrahim Rifath on Unsplash.*

Money is 8-bytes, smallmoney is 4-bytes, decimals with a precision of 1-9 is 5 bytes and decimals with a precision of 10-19 are 9 bytes.

This means that, in those columns with the datatype decimal(10-19,4), one can save either 1 or 5 bytes depending on the data within and the business requirements. So, per million rows this would be a saving of either 122 or 610 pages just by changing the datatype.

Are the money & smallmoney datatypes more processor intensive though than the decimal datatypes?

I wouldn’t say they’re more processor-intensive, no.

integer types are also nicer on cpu cycles for things like comparison and math as most often than not they map to a single cpu instruction. The floating point cpu instructions are approximations of the value and at most can give 6 or 7 digits of precision. If you perform any division at all of numbers, to be “precise” and allocate the division amongst other records, I would make the last one = total before division – (result of the division * denominator – 1). This would eliminate any rounding errors and place them in the last record.

Thanks Steven. It feels like a lot of folks are getting caught up on floating point when in fact MONEY is stored as BIGINT, so all the integer mathematics still applies.

Comments are closed.