Forum Discussion
SQL server 2019 Inconsistent behavior of real datatype value at 5th precision.
We came across a scenario where the real data type values are not showing consistent results and hoping the Microsoft team has answer and workaround for this situation.
Scenarios: In our current process, the data is getting copied from one table to another table and the columns that have an issue is not of the same datatype(Numeric to real) . Here is sample of such table column:
Create table SourceTable as
( ID as integer
.
.
.
Latitude numeric(9,6)
Longitude numeric(9,6)
.
.
.
)
Create table TargetTable as
( ID as integer
.
.
.
fltLatitude real
fltLongitude real
.
.
.
)
The source table is inserting data in Target table , the common and expected scenario would be to round off from numeric column to real which is working good for many scenarios as listed below for data comparison:
SourceTable.Latitude | TargetTable.fltLatitude |
38.859084 | 38.8591 |
35.387115 | 35.3871 |
40.831684 | 40.8317 |
42.495315 | 42.4953 |
42.450725 | 42.45073 |
42.087812 | 42.08781 |
37.758461 | 37.75845 |
Whereas there are certain set of data which are inserted in way which is not really explainable:
SourceTable.Latitude | TargetTable.fltLatitude |
41.999135 | 41.99912 |
40.200084 | 40.2001 |
40.505663 | 40.50568 |
42.270674 | 42.27066 |
42.348207 | 42.34819 |
42.640155 | 42.64014 |
66.083740 | 66.08376 |
42.783758 | 42.78374 |
The difference in 5th precision is making it an observable difference when it comes to downstream analytics involving the above data. I have read some articles about precision issue in SQL server 2016/2017 and Microsoft has recommended to update compatibility level. Currently we are using SQL Server 2019 CU22 with compatibility level 150 which seems pretty reasonable here. Could anyone please provide insight on this issue?
DM_1007 wrote:We came across a scenario where the real data type values are not showing consistent results and hoping the Microsoft team has answer and workaround for this situation.
And that's the normal behaviour and the reason why the "real" data type is called "Approximate-number data type"; it's by design.
See float and real (Transact-SQL) - SQL Server | Microsoft Learn
- Windows 11 system errors, near crash situation, remote help needed urgently!
