Money vs small money datatype in sql server

Money Datatype

Data that represent salary or currency, you can use money data type to hold these values in sql server.

Money can represent in the range from “-922337203685477.5808″ up to “922337203685477.5807″ and takes 8 bytes of space in memory.

SmallMoney Datatype

If you think value will not exceed more than 2,14,748,  you can use another data type that is smallmoney which stores in range of – 214,748.3648 to 214,748.3647. It takes 4 bytes of space in memory.

Example:

declare @MoneyRangeStart money, @MoneyRangeEnd money

select @MoneyRangeStart = ‘-922,337,203,685,477.5807′
select @MoneyRangeEnd = ’922,337,203,685,477.5807′

Select ‘Money Range Start’= @MoneyRangeStart,’ Money Range End ‘= @MoneyRangeEnd

Now if you add any number before “922,337,203,685,477.5807″ as highlighted in below example then you will get error saying arithmetic overflow. Lets execute the below example to confirm the same.

declare @MoneyRange money

select @MoneyRange = ‘12922,337,203,685,477.5807′

Select @MoneyRange

(1 row(s) affected)
Msg 8115, Level 16, State 2, Line 16
Arithmetic overflow error converting expression to data type money.

(1 row(s) affected)

 

Lets go through with example for small money datatype that can range up to 2,14,748. Here if you add any single digit value before range value as highlighted in below example then it will throw error as expected.

Example:

declare @MoneyRange smallmoney

select @MoneyRange = ‘3214,748.3647′

Select @MoneyRange

 

Msg 294, Level 16, State 0, Line 4

The conversion from char data type to smallmoney data type resulted in a smallmoney overflow error.

Leave a Reply

  

  

  


+ 4 = 9