Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What datatypes could be given to this value --> 45.58

Could these be given:

  • numeric
  • money
  • decimal
  • float

What's the difference then? Which one would be the best to use?

share|improve this question
add comment

migrated from stackoverflow.com Feb 13 '12 at 22:03

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 10 down vote accepted

Like everything else in SQL Server, it depends.

Numeric is the same as Decimal. These let you specify precision and are good if you will be having more than 2 decimal places or need more precision than what you indicated.

Money is used for, you guessed it, money. If your field is a currency this is appropriate.

Float is kind of special and is NOT suited for exact numbers. If you need to represent floating point numbers, this is a way to go. If you need to always store exactly what you put in the field, float is a bad choice.

So, what do you want to use the field for? What does your data actually represent? That should be the determining factor in what datatype you use.

share|improve this answer
1  
+1 for it depends! :-) –  marc_s Apr 11 '11 at 16:29
2  
@Marc_s - it really is the only answer for any SQL question... –  JNK Apr 11 '11 at 16:43
    
so if i have a column in which the price of items would be inserted, then shall i use Money datatype or just stick to Decimal? the values inserted would be $5, $12.48, $1.23 etc. –  sqlchild Apr 13 '11 at 8:00
    
can you please give an example of a float value? –  sqlchild Apr 13 '11 at 8:05
    
@sqlchild - if the field will always represent money, then use money. float is used to represent things like 1.79E+308. –  JNK Apr 13 '11 at 10:20
add comment

numeric and decimal are the same thing. They store fixed-precision decimal values. You specify precision and scale (e.g. decimal(8,3) specifies that the field is 8 decimal digits in width and the last 3 digits are right of the decimal point).

Money is similar to numeric/decimal, except that its scale is fixed: it stores 4 decimal points of precision. The underlying data store is, I believe, a signed 64-bit two's complement integer. The decimal point is implied. Smallmoney is a signed 32-bit two's complement integer with an implied 4 decimal places of precision. Despite the name, money/smallmoney aren't just for currency values: if you don't need a ginormous range or high precision, money/small money are your best choices, IMHO. Storage is more compact than the equivalent decimal/numeric value.

float is IEEE floating point. It comes with all advantages and disadvantages possessed by all floating point values. See http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems

I don't believe that any particular format is somehow more better than another: it depends on your needs and the tradeoffs inherent in all of these (decimal/numeric, for instance, chew up a relatively lot of space). There's nothing to stop you from rolling your own, either: you could, for instance, store your 45.58 value as an integer (4558), for instance. Anybody using the field, of course, would need to know that it has an implied scale of 2.

share|improve this answer
    
if i use int , then when i insert 45.58 , then it would change it into a non-decimal value, but i want to store it as it is, because it is the price of an item including tax. if i store it as int, then it would become 4558, read as Four thousand five hundred and fifty eight? –  sqlchild Apr 13 '11 at 8:06
    
Try it. Execute this SQL: select convert(int,45.58) and see what you get. If you want to keep the digits, you need to scale appropriately. –  Nicholas Carey Apr 13 '11 at 17:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.