What represents a double in sql server?

Question

I have a couple of properties in C# which are double and I want to store these in a table in SQL Server, but noticed there is no double type, so what is best to use, decimal or float?

This will store latitude and longitude values, so I need the most accurate precision.

Thanks for the responses so far.

Solution 1

float

Or if you want to go old-school:

real

You can also use float(53), but it means the same thing as float.

("real" is equivalent to float(24), not float/float(53).)

The decimal(x,y) SQL Server type is for when you want exact decimal numbers rather than floating point (which can be approximations). This is in contrast to the C# "decimal" data type, which is more like a 128-bit floating point number.

MSSQL's float type is equivalent to the 64-bit double type in .NET. (My original answer from 2011 said there could be a slight difference in mantissa, but I've tested this in 2020 and they appear to be 100% compatible in their binary representation of both very small and very large numbers -- see https://dotnetfiddle.net/wLX5Ox for my test).

To make things more confusing, a "float" in C# is only 32-bit, so it would be more equivalent in SQL to the real/float(24) type in MSSQL than float/float(53).

In your specific use case... All you need is 5 places after the decimal point to represent latitude and longitude within about one-meter precision, and you only need up to three digits before the decimal point for the degrees. Float(24) or decimal(8,5) will best fit your needs in MSSQL, and using float in C# is good enough, you don't need double. In fact, your users will probably thank you for rounding to 5 decimal places rather than having a bunch of insignificant digits coming along for the ride.

Solution 2

Also, here is a good answer for SQL-CLR Type Mapping with a useful chart.

From that post (by David):

Solution 3

float is the closest equivalent.

SqlDbType Enumeration

For Lat/Long as OP mentioned.

A metre is 1/40,000,000 of the latitude, 1 second is around 30 metres. Float/double give you 15 significant figures. With some quick and dodgy mental arithmetic... the rounding/approximation errors would be the about the length of this fill stop -> "."

Solution 4

You should map it to FLOAT(53)- that's what LINQ to SQL does.

Solution 5

float in SQL Server actually has [edit:almost] the precision of a "double" (in a C# sense).

float is a synonym for float(53). 53 is the bits of the mantissa.

.NET double uses 54 bits for the mantissa.

View more solutions

Comments

[Vishal_Kotecha]

You say float, David says decimal, now I am even more confused :)

[Vishal_Kotecha]

Now it is even more confusing with two different answers :p

[Michael Petrotta]

Don't be confused. This answer is wrong. Decimal is a base-10 type; Float (in SQL Server and the CLR) is a base-2 type.

[Vishal_Kotecha]

This is for storing latitude and longitude values. Does this make a difference?

[Vishal_Kotecha]

I'm using doubles in c# and these values will be latitude and longitude values in their respective columns.

[Fear605]

Actually, IIRC, double in .NET uses a 52-bit mantissa and 11-bit exponent.

[Fear605]

This is just wrong... both float/float(53) in MSSQL and double in C# have approximately 15 digits of precision. They aren't exactly the same, but close enough. Decimal, on the other hand, is complete overkill for storing a double, and it has to go back to base 2 for all calculations in SQL Server, and to come back to C# as a double.

[Euro Micelli]

I'll be darn; you're right! I wonder what SQL does with the extra bit; it's not used for the exponent. If it did, the exponent would go up to +-616 instead of +-308. Maybe to track NULL?

[Euro Micelli]

Now I'm confused. Every piece of evidence points to the idea that they use the same format (as everything else in Windows). And why wouldn't they? I can't find a definite statement on the bitwise representation in SQL Server (besides the help page for float). I'll post a correction if I find out.

[DaveN59]

I stand corrected. What you are saying makes perfect sense to me. Decimal only makes sense if you keep it as a decimal value everywhere. You lose value anytime you make a conversion from base 10 to base 2 (and back).

[DaveN59]

I have to concur, and admit I was off base. I was introduced to decimal in the IBM DB2 world, where decimal is a real data type, supported by all flavors of code and the database on IBM platforms. Not that it isn't a real datatype in the MS world, but it is not supported as well as in teh IBM camp. Sorry for creating any confusion.

[Mars]

Sell your screen tech to Apple and make billions! (Quick arithmetic tells me you have the most extreme resolution I've ever heard of, even at latitudes close to the North/South Pole; about a billion times higher than mine.)

[Gerard ONeill]

Thanks for explaining the difference between the two 'decimals'!

[codekaizen]

"MSSQL float does not have exactly the same precision as the 64-bit double type in .NET (slight difference in mantissa IIRC), but it's a close enough match most uses." Do you have a reference for this? As far as I can see, both use 53 bits of significand, use 64 bits for storage, and have the same bit layout and meaning as IEEE 754.

[d219]

Below link also of use; it has information additional to the above as it shows the SqlDataReader procedures and enumerations (in addition to the .NET and SQL type comparisons) docs.microsoft.com/en-us/dotnet/framework/data/adonet/…

[Mads Ravn]

I'm also quite curious about the question raised by @codekaizen (slight difference in mantissa). Do you have a reference or a test that exemplifies this?

[Fear605]

Back in 2011, I believe I ran across some documentation showing that there was a difference, but testing the current version of C# against Azure, they appear to be binary-compatible, so I've updated my answer.

[Christoph]

@richardtallent: FLOATs depend on the processor! If you have a FLOAT as part of your record ID (bad idea!), it can happen that certain records cannot be fetched by their ID (maybe every 10000th record or so but reproducable). I was working with Phoenix (some hospital database system) where that was the case, they used timestamps that were FLOATs (<days since whatever>.<time as fractals>) as part of the record id. Quite ugly if you cannot retrieve a record reliably with its primary key.

[Fear605]

@Christoph, yes, floats are of course a terrible choice for a key value. But the FLOAT datatype in SQL Server is not processor-dependent, nor does the float or double datatype in C# differ by processor. On some processors, a higher-precision native float type may be used by .NET in float/double math ops, so there can be some minor discrepancies in the results (i.e., getting a more precise answer), but the results in memory are still stored as IEC 60559 floats. docs.microsoft.com/en-us/dotnet/csharp/language-reference/…

[Christoph]

@richardtallent: Even if an expression is deterministic, if it contains float expressions, the exact result may depend on the processor architecture or version of microcode. To ensure data integrity, such expressions can participate only as non-key columns of indexed views. (from docs.microsoft.com/en-us/sql/relational-databases/views/…). Which would hint in the direction that you are right, a single value is not processor dependent, - but still there is the fact that in some constellations a record with a FLOAT id cannot be found through an equals operation.