0

I had a table with two columns for coordinates stored in. These columns were REAL datatype, and I noticed that from my application it was only showing 5 decimals for coordinates, and positions were not accurate enough. I decided to change datatype to FLOAT, so I could use more decimals. It was for my pleasant surprise that when I changed the column data type, the decimals suddenly appeared without me having to store all the coordinates again.

Anyone can tell me why this happens? What happens with the decimal precision on REAL datatype?. Isn´t the data rounded and truncated when inserted? Why when I changed the datatype the precision came up with no loss of data?..

NicoRiff
  • 4,803
  • 3
  • 25
  • 54
  • 1
    Use DECIMAL not FLOAT if you are looking to have precise decimals... but I think the answer is because REAL is almost the same data type as FLOAT. According to the MS Docs, REAL is synonymous with FLOAT(24). – Jacob H May 23 '17 at 17:49
  • 1
    Recent versions of Sql Server have special spacial data types built in for lattitude/longitude. – Joel Coehoorn May 23 '17 at 17:51
  • @JoelCoehoorn makes a good argument for using the new geography data types too. – Jacob H May 23 '17 at 17:52
  • @JoelCoehoorn. Thanks. But why is this happening on the column data type conversion??. – NicoRiff May 23 '17 at 17:54
  • As I explained, the REAL and FLOAT data types are nearly identical in SQL. Even the MS documentation says that REAL is synonymous with FLOAT(24), so most likely your app is interpreting it as such (hence the extra decimal places). – Jacob H May 23 '17 at 17:59

1 Answers1

1

You want to use a Decimal data-type.

Floating point values are caluclated by a value and an exponenent. This allows you have store huge number representations in small amounts of memory. This also means that you don't always get exactly the number you're looking for, just very very close. This is why when you compare floating point values, you compare them within a certain tolerance.

It was for my pleasant surprise that when I changed the column data type, the decimals suddenly appeared without me having to store all the coordinates again.

Be careful, this doesn't mean that the value that was filled in is the accurate value of what you're looking for. If you truncated your original calculation, you need to get those numbers again without cutting off any precision. The values that it autofills when you convert from Real to Float aren't the rest of what you truncated, they are entirely new values which result from adding more precision to the calculation used to populate your Real value.

Here is a good thread that explains the difference in data-types in SQL:

Another helpful link:

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
EMUEVIL
  • 502
  • 3
  • 14