0
142
ViewsWhy does SSMS round results?
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
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; } }
2 answers
Answer question0
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
0
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)