What Represents a Double in SQL Server

What represents a double in sql server?

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.

Which sql server data type best represents a double in C#?

Answer to this and all mapping of types can be found here.

SQL-CLR Type Mapping

Chart, stolen from that page:

---

Updated 1/7/2013 - there's a more recent version, .not so colorful here:
SQL Server Data Type Mappings

How to put double type on SQL server?

I am not sure if this is relevant but I need to store these values with a . not a , to
separate the fractional portion of the values. Is there a setting in
SQL Server that I should be aware of to ensure this happens?

No, it is totally enough to learn programming to the point you realize that this is not a question at all - decimals are stored as decimals. "." or "," are part of the visual conversion (the "ToString" call, so to say) to print the value and have nothing to do with the value.

If you want to store a double, you want to store a double. Point. If you want to make sure your program presents it with a ".", then PROGRAM THE UI PROPERLY, but do not bother SQL Server internal storage with it. Normally they are shown in the locale - which is smarter than hardcoding in most cases. SO, maybvbe you force-change the UI locale? Or hardcode the conversion to apply every time you print out a value.

What SQL Server datatype should I use to store values of this type. Perhaps decimal or
float?

http://msdn.microsoft.com/en-us/library/ms187752.aspx

explains the data types of sql server.

Choose one that fits your requiremnents. Likely a float version with a given precision. Now, if you ar afraid because those are named as "approximate numeric" note that a double IS an approximate numeric, also in C# (or any other front end language you use - you do not tell us).

Default recommended mappings are at http://msdn.microsoft.com/en-us/library/ms131092.aspx and would point towards a "float".

Why is a SQL float different from a C# float

A SQL float is a double according to the documentation for SQLDbType.

I keep getting the same error while trying to create a field type DOUBLE in SQL

double is not a valid data type in sqlserver, use float instead. See

list of data types

in sql server.

create table test7(isaNumber float)

Unexpected results with double casting numeric datatype in SQL Server

OK, first of all, the result in SQL Server 2017 for real_a is not 778881860. It is 778881856, exactly, just as in SQL Server 2008. How this floating-point value is presented by the client is another matter -- Management Studio shows me 7.788819E+08, sqlcmd produces 7.7888186E+8, and apparently SQL Fiddle uses another library altogether (one I would personally have issue with, seeing as how it obscures significant figures!)

This value is not arbitrary. REAL is a single-precision floating point type that cannot represent 778881838.81 exactly. The closest representable value is 778881856, hence your result (the next lower representable value is 778881792). Without casting to INT, you can see this value using

SELECT STR(CONVERT(REAL, CONVERT(NUMERIC(38, 6), 778881838.810000)), 40, 16)

778881856.0000000000000000

Your use of the term "double" makes me think you're confusing this with FLOAT, which is the double-precision floating point type. FLOAT cannot represent this value exactly either, but it comes much closer:

SELECT STR(CONVERT(FLOAT, CONVERT(NUMERIC(38, 6), 778881838.810000)), 40, 16)

778881838.8099999400000000

Converting this value to an INT yields the (truncated) 778881838. (This truncation is documented and does not happen for conversions to NUMERIC; you'll need to ROUND first before converting if you'd prefer 778881839 instead.)

---

Related Topics