SQL Server FLOAT Data Type
Use the SQL Server FLOAT data type to define columns, variables, and parameters storing floating-point numbers.
By floating point, we mean, numbers that have no fixed decimal place.
Consider using SQL FLOAT when working with scientific values. Unlike DECIMAL, the FLOAT type handles a wide range of numbers:
Here is the range expressed in scientific notation:
|Sign||Max Value||Minimum Value|
If you look here, float can handle all the way up to 306 zeros. So, basically, 1.79 times 10 to the 308th power. Imagine the n umber 179 followed by 306 zeros.
Here it is…
When working with FLOAT keep in mind rounding issues. For example, though 2.50 is considered a FLOAT, you may find, that the number isn’t exactly represented as 2.50 in memory. Instead, you may see the number output as 2.499999.
So, it becomes kind of tricky when you’re comparing values with FLOAT, as the internal representation may be that one decimal place off.
Though FLOAT is great for engineering and scientific application, where calculation prevail, use DECIMAL for financial applications.
Converting FLOAT Values
Here are some things to keep in mind when converting SQL SERVER FLOAT types:
- Converting to integer truncates a FLOAT type.
- Consider using STR over CAST when converting to character data. There are more formatting options.
- Though no longer a restriction, prior to SQL Server 2016, a 17 digit conversion restriction held. Floats less than 5E-18 round down to zero (e.g. 0.0000000000000000050000000000000005).
Additional SQL Server Float Resources
To learn more about SQL Server data types, check out these useful resources:
- Common Data Types, such as float, used in SQL Server
- How do I handle a “Error converting data type” error?
- Recommended book – SQL Queries for Mere Mortals