Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
david.raimosson
Starting Member
2 Posts |
 Posted - 2010-07-05 : 11:53:38
|
| Hi, I was confronted with creating a salary user defined type (UDT). The value couldn't exceed 10 millions. The best choice of data type should be a float(24) was my thought. However, a Microsoft product stated that in this particular case a decimal(8) would be better becuase it would require LESS storage space! According to http://msdn.microsoft.com/en-us/library/ms173773.aspx and http://msdn.microsoft.com/en-us/library/ms187746.aspx the storage spaces are 4 and 8 bytes for the float and decimal respectively. Do I misunderstand something? Any help appreciated. Best regards /David |
|
|
KrafDinner
Starting Member
34 Posts |
Posted - 2010-07-05 : 12:13:46
|
If my thinking is correct here, you would want to use dec(10,2) in this case in order to store the actual decimal part of the salary. If you aren't interested in the decimal portion, float (or even int) seems like it would definitely be the way to go.I am as confused as you by the recommendation of decimal over float in this case...  |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-07-05 : 12:39:15
|
Also, FLOAT/REAL are unprecise for the fractional part and my not accurately store the decimals correct.It uses an IEEE implementation SUM(1 / 2^n) to calculate the nearest "binary" decimal value.DECIMAL(9, 2) would give range between 0.00 and 9,999,999.99 and occupy 5 bytes.REAL would give same range (and some more) with possible loss of decimal precision.If decimals are not an option (as your decimal(8) datatype indicate) I would go for INT (4 bytes).
N 56°04'39.26"E 12°55'05.63" |
|
|
|
david.raimosson
Starting Member
2 Posts |
Posted - 2010-07-05 : 14:34:32
|
| Hi, thanks for replies. If I had read the specifications a bit closer I would have realized that the float(24) supports values up to and exceeding 10 millions but has only a precision of 7 digits what so ever. So, the float(53), using 8 bytes, would have been required and then the decimal(10,2) beats it. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-07-05 : 15:11:51
|
You will save 4 bytes per instance of using (9, 2) instead.What's the difference between 10,000,000.00 and 9,999,999.99 ?Unless you want to play safe because (10, 2) can manage up to 99,999,999.99
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|
|
|
|
CREATE TYPE