1

I have a table with field Latitude of real datatype.

When I query it from SQL Server Management Studio, I get the following:

select Latitude from Location where ID = 123

enter image description here

As you can see a value of 26.09418 is returned. However, if I write a simple console in .NET and retrieve the data from the database, it returns 26.094183. It brings back an additional significant number.

Why the mismatch? Is there a way to stop SSMS from doing it?

For reference, here is the c# code to retrieve the data (using Dapper):

string sql = "select Latitude from Location where ID = 123";

using (var connection = new SqlConnection(conn)) {
    var location = connection.QueryFirst<Location>(sql);
    Console.WriteLine(location.Latitude);
}

class Location { public Single Latitude { get; set; } }
AngryHacker
  • 59,598
  • 102
  • 325
  • 594
  • 1
    Could not reproduce. https://i.stack.imgur.com/HhzCw.png. – Robert Harvey Jul 20 '21 at 18:19
  • Common issue with REAL and/or FLOAT. Consider a DECIMAL with a fixed precision. https://stackoverflow.com/questions/1056323/difference-between-numeric-float-and-decimal-in-sql-server and Just for Fun: https://xkcd.com/2170/ – John Cappelletti Jul 20 '21 at 18:21
  • @RobertHarvey If I run `Str(Latitude,20,16)`, i get `26.0941829681396480` - which is the true representation of what's in the DB (based on the link in @Rafael's answer). What do you have? – AngryHacker Jul 20 '21 at 18:55

2 Answers2

2

It would be better to store your data either with the precise decimal data type for a known scale and precision, or use float.

Saying that, you can workaround it and get your desired output in SSMS simply using str

create table t(x real)
insert into t values(26.094183)

select * from t

26.09418

select Str(x,9,6) from t

26.094183

Stu
  • 30,392
  • 6
  • 14
  • 33
1

The REAL type is treated as a FLOAT(24).

The FLOAT(24) datatype, or smaller, reacts the same way. The first thing to remember when experimenting with floating point numbers in SQL Server is that SSMS renders a floating point number in a way that disguises small differences

Found in red-gate.com - Calculations using the REAL datatype (single precision)

gunr2171
  • 16,104
  • 25
  • 61
  • 88
Rafael Souza
  • 59
  • 5