CODEWITHSUNDEEP
sqlsql-servervarcharsqldatatypes
varun
varun

Reputation: 135

SQL Server - float vs varchar

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?

Upvotes: 3

Views: 6150

Answers (2)

David Dubois
David Dubois

Reputation: 3932

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?

Upvotes: 5

marc_s
marc_s

Reputation: 754418

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!

Upvotes: 4

Related Questions