How SQL Server stores data types: money

A stack of coins

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 0x7CA8B00200000000
4513.19 SMALLMONEY 0x7CA8B002
4513.19 DECIMAL(19,4) 0x017CA8B00200000000
4513.19 DECIMAL(10,4) 0x017CA8B00200000000
45131900 BIGINT 0x7CA8B00200000000
45131900 INT 0x7CA8B002

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 0x84574FFDFFFFFFFF
-4513.19 SMALLMONEY 0x84574FFD
-4513.19 DECIMAL(19,4) 0x007CA8B00200000000
-4513.19 DECIMAL(10,4) 0x007CA8B00200000000
-45131900 BIGINT 0x84574FFDFFFFFFFF
-45131900 INT 0x84574FFD

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.

2 thoughts on “How SQL Server stores data types: money

  • 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?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: