5

what's the difference between money and bigint data type in sql server?

sqlchild
  • 8,754
  • 28
  • 105
  • 167

2 Answers2

14

Range/Precision

bigint range is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

money range is -922,337,203,685,477.5808 to 922,337,203,685,477.5807

Storage

They both take 8 bytes of storage and are stored as big integers the only difference is that SQL Server understands that the 4 right most digits are after the decimal point.

If you do

declare @m money = 1.2345
declare @b bigint = 12345

select cast(@m as BINARY(8)), cast(@b as BINARY(8))

You see the storage is the same.

------------------ ------------------
0x0000000000003039 0x0000000000003039

Behaviour

However the money datatype does not behave exactly as though you were to just use a bigint yourself. Calculations with money are to be avoided but they are still more precise than the analogous integer division.

declare @m money = 1.9999, @m2 money = 1

select @m/@m2 /*Returns 1.9999*/

declare @b bigint = 19999, @b2 bigint = 10000

select @b/@b2 /*Returns 1*/
Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
0

I think its pretty simple; bigint can't hold decimal point however money can.

Example

bigint -------------- money

44 (correct) ------- 44 (correct) and in db 44.0000 will be saved

4.4 (incorrect) ---- 4.4(correct) and in db 4.4000 will be saved

Waqas Raja
  • 10,802
  • 4
  • 33
  • 38
  • This is wrong. Money does *not* store a decimal point, it is literally stored as a bigint. The difference is that for money SQL Server interprets the last four digits of the bigint as being to the right of a "virtual" decimal point. – Nathan Griffiths May 30 '17 at 23:21