How SQL Server stores data types: money

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:

• Bit columns
• Dates and times
• Integers and decimals

🎶 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:

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.

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.