In SQL Server, I have decimal data to be stored in a table (which is never used for joins or filtering). This decimal data is variable - 80% of the time it has single digit values (1, 4, 5) and remaining 20% are with 16 digit decimals (0.8999999761581421, 3.0999999046325684).
I am wondering If I can save any storage space going with varchar instead of float, or if I should stick with float since this is numeric data?
Here's an interesting observation:
Start with the mathematical value 0.9
Convert that to a binary number. For the same reason that 1/3 cannot be expressed in a finite number of digits in base 10, the number 0.9 cannot be expressed in a finite number of digits in base 2. The exact mathematical value is:
0.1 1100 1100 1100 1100 1100 1100 1100 ... with the "1100" repeating forever.
Let's store this value in an IEEE-754 single-precision floating-point value. (In SQL Server, this is called REAL type). To do that we have to round to 23 significant bits. The result is:
0.1 1100 1100 1100 1100 1100 11 Convert this to its exact decimal equivalent, you get this:
0.89999997615814208984375 Round that to 16 places after the decimal point. You get:
0.8999999761581421 Which is coincidentally the value you show as your example.
If you do the same thing to 3.1, you get 3.0999999046325684
Is it possible that all your inputs are simply numbers with one digit after the decimal point, which have been stored as a floating-point value, and then converted back into decimal?
Always use the most appropriate datatype! Since this is clearly numerical data - use a numerical type. This will allow to e.g. sum the values, order by those values - those are numbers - so treat and store them as such!!
If you need to support fractions, you could use FLOAT or REAL, but those are notorious for rounding errors etc. Using DECIMAL(p,s) avoids those pitfalls - it's stable, it's precise, not prone to rounding errors. So that would be my logical choice.
See the official MS docs for DECIMAL for your details on how to define the p (precision - total number of digits overall) and s (scale - number of digits after the decimal point).
And btw: those are stored in fewer bytes that a varchar column large enough to hold these values would be!
decimal will most likely use FEWER space than a comparable varchar - all the more reason to pick the numerical datatypeSELECT by one of these columns - if you use a varchar for this - good luck! - this just causes endless and needless grief and headaches - don't do it. If it looks like a number, quacks like a number, smells like a number - IT IS a number and then you should by all means treat and store it as a number
decimal<>float. By "I have decimal data" do you really mean "I have float data"? If not, is your data actually adecimaland not afloat? Either way, never use avarcharto store numerical data.FLOATor go withDECIMAL, never use a string type to store numerical data, regardless of storage considerations. The only time that might be appropriate is if you have a very specific need to store data received from an external system exactly as you got it, character for character, even if it also has a numeric value. This is clearly not one of those cases. If storage was even a concern at all, you should consider things like row and page compression before the data type, simply because mixing up strings and numbers is such a huge source of pain.