0

I'm expanding on a similar question titled Is SQL Server 'MONEY' data type a decimal floating point or binary floating point?

The accepted answer told us that the "money" datatype is fixed-point rather than floating-point type, but didn't say whether it was binary-fixed-point or decimal-fixed-point.

I'm assuming it's decimal-fixed-point but I can't find confirmation of this anywhere.

The documentation tells us the range and size, but not the underlying implementation.

samHumeniuk
  • 111
  • 9
  • I've never heard of `binary-fixed-point`. Please reference what this is meant to be. Binary is base one rather than base ten. Regardless it's the same number. – Nick.Mc Feb 11 '20 at 10:52
  • In [Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server?](https://stackoverflow.com/q/582797/1115360) (TL;DR: do not use MONEY), [user Anon tells us](https://stackoverflow.com/a/15096625/1115360) that "Behind the scenes, money/smallmoney are just a bigint/int The decimal point in the text representation of money is visual fluff..." – Andrew Morton Feb 11 '20 at 10:53
  • 1
    It's not a coincidence that the smallest and largest values correspond exactly to those of `BIGINT` with a decimal point inserted, because that's basically what it is (and `SMALLMONEY` is an `INT` with a decimal point inserted). As the scaling factor is a power of 10, it qualifies as a decimal fixed-point type. As linked answers say, `MONEY` should be used with caution exactly because of its fixed precision (`DECIMAL` calculations will vary their precision as needed). – Jeroen Mostert Feb 11 '20 at 10:53
  • Can this link help you? https://www.w3computing.com/sqlserver2012/data-types-sql-components/ – Mohamad TAGHLOBI Feb 11 '20 at 10:58
  • @samHumeniuk . . . Interesting. I had assumed that it stored the value the same as `decimal`s. But given the limits on the values, it would appear to be a different internal format. You may need to dive into the SQL Server internals to answer this question. – Gordon Linoff Feb 11 '20 at 12:03
  • @Nick.McDermaid, Hi Nick, thanks for your comment. I'm not very hot on this topic myself. I was going by what [wikipedia](https://en.wikipedia.org/wiki/Fixed-point_arithmetic) says in the paragraph "Binary vs. decimal". So a fixed point number that has scaling factor that is power of 10 is a decimal fixed-point. A fixed point number that has a scaling factor that is a power of 2 is a binary fixed-point.. – samHumeniuk Feb 11 '20 at 14:30

1 Answers1

3

Not sure why you care about the underlying implementation but you can CAST a money data type value to binary(8) to see the value's bits:

DECLARE @money money;;

--same as min 64-bit signed integer (2's compliment) with 4 decimal places assumed
SET @money = -922337203685477.5808;
SELECT CAST(@money AS binary(8)); --0x8000000000000000

--same as max 64-bit signed integer with 4 decimal places assumed
SET @money = 922337203685477.5807
SELECT CAST(@money AS binary(8)); --0x7FFFFFFFFFFFFFFF

So money looks to be a 64 bit signed integer with 4 decimal places assumed. The precision/scale is not included with the value with money (and it's smallmoney cousin).

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • Side note to this -- although conversion to `binary` does tell you something about how values are represented, it is not the complete story. It happens to be for `MONEY`, but not for `DECIMAL` -- converting those to `BINARY` types includes bytes for precision and scale and pads the value for alignment, none of which is part of the representation when stored on a data page. Scale and precision are part of the rowset metadata, not the value. (`DECIMAL` still takes up more space for other reasons, most prominently a separate sign byte and the non-fixed binary scale factor). – Jeroen Mostert Feb 11 '20 at 12:24
  • @JeroenMostert, right, the in-memory and on-disk format varies along with the endian ugliness. Not to mention the structure when passed over the wire TDS. – Dan Guzman Feb 11 '20 at 12:27
  • Yup. My point being that your last sentence is actually wrong -- precision/scale are *not* included with the value, which is why that's not the reason `money` takes up less storage. (I'm assuming you weren't talking in-memory storage of individual values there, as that's almost never relevant -- it only comes into play when the engine decodes the data pages, and then transiently.) – Jeroen Mostert Feb 11 '20 at 12:32
  • @JeroenMostert, didn't I say precision/scale *is not* included in the money value? – Dan Guzman Feb 11 '20 at 12:40
  • Sorry, I need to be more clear. You say "unlike `decimal`, the precision/scale is not included with the value", implying that for `decimal`, the precision/scale *is* included with the value, and this accounts for the storage difference. It does not. The smallest `decimal` type encompassing `money` (`decimal(19, 4)`) takes up 9 bytes of storage as opposed to `money`'s 8, 1 for the sign byte and 8 for the value. Precision and scale are only metadata. – Jeroen Mostert Feb 11 '20 at 12:42
  • @JeroenMostert, I see where you're coming from. I removed the decimal storage part from my answer, which was the native decimal structure for BCP. – Dan Guzman Feb 11 '20 at 12:57