CODEWITHSUNDEEP
c#sqlsql-serversql-server-2005t-sql
Xaisoft
Xaisoft

Reputation: 46591

What represents a double in sql server?

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.

Upvotes: 391

Views: 511038

Answers (9)

CodeAdze
CodeAdze

Reputation: 39

I believe the overarching correct answer is none. There is no Sql Server data type that can store a Double. From the OP's requirements of storing latitude and longitude the suggestion of using a float is good, but remember that a Double can hold Positive Infinity, Negative Infinity, and NaN. You will be in a world of hurt if you try to set a Sql Float to one of those values.

Upvotes: 0

richardtallent
richardtallent

Reputation: 35374

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.

Upvotes: 455

gbn
gbn

Reputation: 432230

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

Upvotes: 16

Md Shoriful Islam
Md Shoriful Islam

Reputation: 17

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

Upvotes: -2

Mazdak Shojaie
Mazdak Shojaie

Reputation: 1698

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

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

Upvotes: 88

Euro Micelli
Euro Micelli

Reputation: 33998

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.

Upvotes: 9

David Basarab
David Basarab

Reputation: 73301

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.

Upvotes: 5

DaveN59
DaveN59

Reputation: 3718

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.

Upvotes: 4

RichardOD
RichardOD

Reputation: 29157

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

Upvotes: 10

Related Questions