385

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.

sll
  • 61,540
  • 22
  • 104
  • 156
Xaisoft
  • 45,655
  • 87
  • 279
  • 432
  • Here are the CLR datatype mappings to SQL Server: [http://msdn.microsoft.com/en-us/library/system.data.sqldbtype.aspx](http://msdn.microsoft.com/en-us/library/system.data.sqldbtype.aspx) – Achilles Jul 30 '09 at 20:39
  • There is a [great thread on MSDN](http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/6dee81ab-b205-48cb-9058-37a2eec0eb6e) describing the primary difference between FLOAT and DECIMAL. In short, Float is approximate and cannot represent some values. Look at the accepted answer. – Matthew Jones Jul 30 '09 at 20:39

8 Answers8

444
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.

richardtallent
  • 34,724
  • 14
  • 83
  • 123
  • 2
    You say float, David says decimal, now I am even more confused :) – Xaisoft Jul 30 '09 at 20:36
  • I'm using doubles in c# and these values will be latitude and longitude values in their respective columns. – Xaisoft Jul 30 '09 at 20:48
  • 3
    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. – DaveN59 Jul 31 '09 at 18:32
  • Thanks for explaining the difference between the two 'decimals'! – Gerard ONeill Jun 20 '14 at 14:32
  • 2
    "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. – codekaizen Feb 22 '17 at 20:44
  • 2
    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? – Mads Ravn Jun 10 '20 at 12:38
  • 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. – richardtallent Jun 11 '20 at 02:01
  • 1
    @richardtallent: `FLOAT`s 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 `FLOAT`s (. – Christoph Jun 16 '22 at 15:38
  • @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. https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/language-specification/types#837-floating-point-types – richardtallent Jun 30 '22 at 15:01
  • @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 https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-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. – Christoph Jun 30 '22 at 15:51
  • @Christoph, yes, though it would probably be a bad idea to try to use an "equals" operation on a GPS coordinate, so that shouldn't be a problem for this use case. – richardtallent Sep 23 '22 at 13:32
  • `real` differs from `float`: `select cast(0.56 as real), cast(0.56 as float)` gives `0.56 0.56000000000000005` – darw Sep 27 '22 at 14:09
84

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

From that post (by David): enter image description here

Community
  • 1
  • 1
Mazdak Shojaie
  • 1,620
  • 1
  • 25
  • 32
  • 1
    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) https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings – d219 Jul 31 '19 at 08:38
15

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 -> "."

Jean-François Fabre
  • 137,073
  • 23
  • 153
  • 219
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    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.) – Jonas Byström Apr 02 '13 at 09:44
10

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

RichardOD
  • 28,883
  • 9
  • 61
  • 81
9

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.

Euro Micelli
  • 33,285
  • 8
  • 51
  • 70
  • 2
    Actually, IIRC, double in .NET uses a 52-bit mantissa and 11-bit exponent. – richardtallent Jul 30 '09 at 20:51
  • 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 Jul 30 '09 at 21:42
  • 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. – Euro Micelli Jul 30 '09 at 22:07
5

For SQL Sever:

Decimal Type is 128 bit signed number Float is a 64 bit signed number.

The real answer is Float, I was incorrect about decimal.

The reason is if you use a decimal you will never fill 64 bit of the decimal type.

Although decimal won't give you an error if you try to use a int type.

Here is a nice reference chart of the types.

David Basarab
  • 72,212
  • 42
  • 129
  • 156
4

It sounds like you can pick and choose. If you pick float, you may lose 11 digits of precision. If that's acceptable, go for it -- apparently the Linq designers thought this to be a good tradeoff.

However, if your application needs those extra digits, use decimal. Decimal (implemented correctly) is way more accurate than a float anyway -- no messy translation from base 10 to base 2 and back.

DaveN59
  • 3,638
  • 8
  • 39
  • 51
  • This is for storing latitude and longitude values. Does this make a difference? – Xaisoft Jul 30 '09 at 20:44
  • 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. – richardtallent Jul 30 '09 at 21:06
  • 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 Jul 31 '09 at 18:28
-3

A Float represents double in SQL server. You can find a proof from the coding in C# in visual studio. Here I have declared Overtime as a Float in SQL server and in C#. Thus I am able to convert 

int diff=4;
attendance.OverTime = Convert.ToDouble(diff);

Here OverTime is declared float type

d219
  • 2,707
  • 5
  • 31
  • 36
Md Shoriful Islam
  • 7
  • 1