What datatypes could be given to this value --> 45.58
Could these be given:
What's the difference then? Which one would be the best to use?
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 only takes a minute to sign up.Sign up to join this community
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.
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.