6419

How to store value in MSSQL 2008 with high precision?

Question:

I have Double type value in VisualStudio side .

I need to store this value in MSSQL 2008 in float column type.

The problem is that precision of my value is seventeen digits after the point,

in SQL side the few last digits is cut and rounded.

Any idea how to make SQL server to keep value as is (i.e. not to make changes in last digits)?

Thank you in advance!

Answer1:

The .Net <a href="http://msdn.microsoft.com/en-gb/library/678hzkk9%28v=vs.110%29.aspx" rel="nofollow">Double</a> and the MSSQL server <a href="http://msdn.microsoft.com/en-us/library/ms173773.aspx" rel="nofollow">float</a> are approximate numeric data types.

They offer an efficient way of storing <a href="http://en.wikipedia.org/wiki/Real_number" rel="nofollow">"real"</a> numbers. Internally they are stored as a two exact binary numbers, the base (matissa) and a multiplier (exponent). Thier precision varies based on the relative size of these two exact binary numbers, especially the mantissa. Operations using these <a href="http://en.wikipedia.org/wiki/Floating_point" rel="nofollow">floating point numbers</a> are optimised on modern processors, especially GPUs.

At the small end of the number there is not a binary value for every real number in the range. When the respective ratios used to store the numbers differ, these gaps are interlaced and you get small rounding errors converting the numbers between ratios. With floating point numbers thats just the way it is. When floating point numbers are used to represent natural data a quintillionth either way doesn't make much difference.

If you really need the extra precision you should use the MSSQL <a href="http://msdn.microsoft.com/en-us/library/ms187746.aspx" rel="nofollow">decimal</a> or <a href="http://msdn.microsoft.com/en-us/library/ms187746.aspx" rel="nofollow">numeric</a> and the .Net <a href="http://msdn.microsoft.com/en-us/library/364x0z75%28v=vs.110%29.aspx" rel="nofollow">Decimal</a> type. However, they use more space and have lower performance than floating points.

Answer2:

Use Decimal data type instead of float.

Here's an example

DECIMAL(30, 17)

30 = maximum total number of decimal digits

17 = maximum number of decimal digits that can be stored to the right of the decimal point.

More on this

<a href="http://msdn.microsoft.com/en-us/library/aa258832(v=sql.80).aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/aa258832(v=sql.80).aspx</a>

Answer3:

FLOAT is not exact. For high precision, you should store them as <a href="http://msdn.microsoft.com/en-us/library/ms187746%28v=sql.100%29.aspx" rel="nofollow">DECIMAL / NUMERIC</a> on the DB.

Answer4:

You won't get precision from a FLOAT type because float is an approximate data type

<blockquote>

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly

</blockquote>

You could use <a href="http://msdn.microsoft.com/en-us/library/ms187746%28v=sql.100%29.aspx" rel="nofollow">decimal</a>.

Recommend

  • Source file (.py) not compiling (.pyc not updated)
  • MySpace DOM?
  • After message type e, program doesn't return to selection-screen ABAP
  • When is locking on types a good idea?
  • Android application not restoring state when installed from .apk, works fine from eclipse
  • Is it safe to accept URL parameters for populating the `url_for` method?
  • MySQL: Difference between `… ADD INDEX(a); … ADD INDEX(b);` and `… ADD INDEX(a,b);`?
  • Is there a way to call library thread-local init/cleanup on thread creation/destruction?
  • Backward compatibility of Python 3.5 for external modules
  • How to add closing tag for canvas in three js rendered Canvas?
  • Accessing Rows In A LINQ Result Without A Foreach Loop?
  • Python cosine function precision [duplicate]
  • HttpClient: disabling chunked encoding
  • Does Apportable support to build library binary (.a/.so)?
  • In matplotlib, how do you change the fontsize of a single figure?
  • Hibernate to update table schema
  • blocking spam referer domain with web.config
  • Spring: No transaction manager has been configured
  • Trying to get the char code of ENTER key
  • Cast between interfaces whose interface signatures are same
  • how does System.Web.HttpRequest::PathInfo work?
  • accepts_nested_attributes_for practical form use for in Rails 3
  • Object and struct member access and address offset calculation
  • Breaking out column by groups in Pandas
  • How solve “Qt: Untested Windows version 10.0 detected!”
  • Keep this build forever option - Jenkins
  • Converting a WriteableBitmap image ToArray in UWP
  • Abort upload large uploads after reading headers
  • Time complexity of a program which involves multiple variables
  • C++ Partial template specialization - design simplification
  • Is there any way to access browser form field suggestions from JavaScript?
  • Splitting given String into two variables - php
  • NetLogo BehaviorSpace - Measure runs using reporters
  • Javascript simulate pressing enter in input box
  • Is my CUDA kernel really runs on device or is being mistekenly executed by host in emulation?
  • Javascript convert timezone issue
  • Hazelcast - OperationTimeoutException
  • Build own AppleScript numerical error handling
  • How do you troubleshoot character encoding problems?
  • How can I get HTML syntax highlighting in my editor for CakePHP?