DECIMAL v DOUBLE

I'm pretty new to SQL and am a little confused as to which field type I 
should use to store my 'currency' type data.

What's the main difference between these two data types and which would best 
fit the purpose?

I want to store signed numerical data with up to 6 decimal places.

Thanks in advance for any help. 


0
Andy
12/1/2009 10:32:41 PM
sqlserver.programming 1873 articles. 0 followers. Follow

6 Replies
696 Views

Similar Articles

[PageSpeed] 49

DECIMAL is an exact numeric, whereas DOUBLE is approximate - which means 
that whilst you can store e.g. 1.234567 in a decimal and it's equal to 
1.234567, for a double this may be stored as 1.23456699999999.

Doubles can cause grief with comparisons (such as x = <some value>, x <= 
<some value>), where the imprecision of the data type might make two 
seemingly identical values differ.

For monetary values, I always use DECIMAL, or MONEY / SMALLMONEY.

"Andy Trezise" <andy@trezise.f2s.com> wrote in message 
news:eJgtyYtcKHA.4780@TK2MSFTNGP04.phx.gbl...
> I'm pretty new to SQL and am a little confused as to which field type I 
> should use to store my 'currency' type data.
>
> What's the main difference between these two data types and which would 
> best fit the purpose?
>
> I want to store signed numerical data with up to 6 decimal places.
>
> Thanks in advance for any help.
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus 
> signature database 4652 (20091201) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
> 



__________ Information from ESET NOD32 Antivirus, version of virus signature database 4652 (20091201) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com




0
Will
12/1/2009 10:44:22 PM
Thanks....understood.

And there's no problem soring negative numbers I assume?

"Will Alber" <junk@crazy-pug.co.uk> wrote in message 
news:ujWjWftcKHA.744@TK2MSFTNGP05.phx.gbl...
> DECIMAL is an exact numeric, whereas DOUBLE is approximate - which means 
> that whilst you can store e.g. 1.234567 in a decimal and it's equal to 
> 1.234567, for a double this may be stored as 1.23456699999999.
>
> Doubles can cause grief with comparisons (such as x = <some value>, x <= 
> <some value>), where the imprecision of the data type might make two 
> seemingly identical values differ.
>
> For monetary values, I always use DECIMAL, or MONEY / SMALLMONEY.
>
> "Andy Trezise" <andy@trezise.f2s.com> wrote in message 
> news:eJgtyYtcKHA.4780@TK2MSFTNGP04.phx.gbl...
>> I'm pretty new to SQL and am a little confused as to which field type I 
>> should use to store my 'currency' type data.
>>
>> What's the main difference between these two data types and which would 
>> best fit the purpose?
>>
>> I want to store signed numerical data with up to 6 decimal places.
>>
>> Thanks in advance for any help.
>>
>>
>> __________ Information from ESET NOD32 Antivirus, version of virus 
>> signature database 4652 (20091201) __________
>>
>> The message was checked by ESET NOD32 Antivirus.
>>
>> http://www.eset.com
>>
>>
>>
>
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus 
> signature database 4652 (20091201) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>
> 


0
Andy
12/1/2009 11:04:58 PM
Nope.

"Andy Trezise" <andy@trezise.f2s.com> wrote in message 
news:eJWa2qtcKHA.1592@TK2MSFTNGP06.phx.gbl...
> Thanks....understood.
>
> And there's no problem soring negative numbers I assume?
>
> "Will Alber" <junk@crazy-pug.co.uk> wrote in message 
> news:ujWjWftcKHA.744@TK2MSFTNGP05.phx.gbl...
>> DECIMAL is an exact numeric, whereas DOUBLE is approximate - which means 
>> that whilst you can store e.g. 1.234567 in a decimal and it's equal to 
>> 1.234567, for a double this may be stored as 1.23456699999999.
>>
>> Doubles can cause grief with comparisons (such as x = <some value>, x <= 
>> <some value>), where the imprecision of the data type might make two 
>> seemingly identical values differ.
>>
>> For monetary values, I always use DECIMAL, or MONEY / SMALLMONEY.
>>
>> "Andy Trezise" <andy@trezise.f2s.com> wrote in message 
>> news:eJgtyYtcKHA.4780@TK2MSFTNGP04.phx.gbl...
>>> I'm pretty new to SQL and am a little confused as to which field type I 
>>> should use to store my 'currency' type data.
>>>
>>> What's the main difference between these two data types and which would 
>>> best fit the purpose?
>>>
>>> I want to store signed numerical data with up to 6 decimal places.
>>>
>>> Thanks in advance for any help.
>>>
>>>
>>> __________ Information from ESET NOD32 Antivirus, version of virus 
>>> signature database 4652 (20091201) __________
>>>
>>> The message was checked by ESET NOD32 Antivirus.
>>>
>>> http://www.eset.com
>>>
>>>
>>>
>>
>>
>>
>> __________ Information from ESET NOD32 Antivirus, version of virus 
>> signature database 4652 (20091201) __________
>>
>> The message was checked by ESET NOD32 Antivirus.
>>
>> http://www.eset.com
>>
>>
>>
>>
>
> 

0
Russell
12/2/2009 12:07:17 AM
> The short answer is that you should use DECIMAL(s,p). =A0This allows
> some extra digits in computations while NUMERIC (s,p) does not.

From SQL Server Books Online

"numeric is functionally equivalent to decimal."
0
Mark
12/3/2009 4:56:56 PM
On the SQL Server platform NUMERIC and DECIMAL are synonomous.

-- 
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption" 
(http://www.apress.com/book/view/1430224649)
----------------

"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:5d0a04f7-da05-43ec-9d8d-88b9535b9a14@o10g2000yqa.googlegroups.com...
>>> I'm pretty new to SQL and am a little confused as to which field [sic] 
>>> type I  should use to store my 'currency' type data.  <<
>
> Welcome to a whole new way of programming!  Columns are not fields;
> columns have data types, constraints, DRI actions, etc. while fields
> get meaning only from the application program reading them. Use the
> right words and you will get the right mindset.
>
> The short answer is that you should use DECIMAL(s,p).  This allows
> some extra digits in computations while NUMERIC (s,p) does not.  The
> rules for Euros need 5 decimal places and that works for GAAP in the
> US.
>
> Never use DOUBLE PRECISION.  It is specifically illegal in some
> places, but it is subject to slooooow performance and rounding errors,
> like all floating point numbers.
>
> Never use the proprietary MONEY data type; it has math problems among
> other things.
>
> I have a few chapters on data types in SQL FOR SMARTIES that might
> help you; get a copy. 

0
Michael
12/4/2009 12:07:04 AM
Decimal & numeric are synonyms, but your remarks about both double and money 
are accurate.

Double, short for double precision floating point, is a data type based on 
an IEEE standard for a 64-bit floating point value with 11-bit exponent and 
52-bit mantissa.  This format was first implemented by Intel in the 8087 
co-processor ~30 years ago, has been remarkably resilient, and is ubiquitous 
in desktop apps but has a major flaw for DBMS: the domain is asymmetrically 
sparse.  The consequence of this is that there are values that cannot be 
represented reliably and the precision and granularity depend on the 
location of the value in the range - okay if you need approximate results, 
but very bad if you want to store a certain value and retrieve it (exactly) 
later.

As you mention, the MONEY data type has truncation issues and is of no 
practical use for any serious computations.  The most useful data types are 
numeric(19,9), numeric(28,9) and numeric(38,9) and similar - adjusted for 
the scale of your particular application because the maximize the fixed 
precision for given storage size in MS SQL server; but even these suffer 
from the problem of decimal to binary base conversion and depending on the 
values can exhibit catastrophic truncation thru various mathematical 
operations (especially division and higher order multiplicative operations). 
They are good at storing a specific value and retuning it exactly within a 
defined precision though, and for DBMS, that is key.  For serious 
calculations, the use of a math library on the application side is highly 
recommended.



"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:5d0a04f7-da05-43ec-9d8d-88b9535b9a14@o10g2000yqa.googlegroups.com...
>>> I'm pretty new to SQL and am a little confused as to which field [sic] 
>>> type I  should use to store my 'currency' type data.  <<
>
> Welcome to a whole new way of programming!  Columns are not fields;
> columns have data types, constraints, DRI actions, etc. while fields
> get meaning only from the application program reading them. Use the
> right words and you will get the right mindset.
>
> The short answer is that you should use DECIMAL(s,p).  This allows
> some extra digits in computations while NUMERIC (s,p) does not.  The
> rules for Euros need 5 decimal places and that works for GAAP in the
> US.
>
> Never use DOUBLE PRECISION.  It is specifically illegal in some
> places, but it is subject to slooooow performance and rounding errors,
> like all floating point numbers.
>
> Never use the proprietary MONEY data type; it has math problems among
> other things.
>
> I have a few chapters on data types in SQL FOR SMARTIES that might
> help you; get a copy. 

0
m
12/4/2009 3:03:00 AM
Reply:

Similar Artilces:

Fill/Expand Selection adding placeholders to Decimals
Hi, I set up a worksheet for a Lookup Function that contains numbers increasing by 0.1 from 100.0 to 50.0. I used the drag and fill to set up the numbers. I noticed some incorrect entries in the results from the look up and found that on certain numbers it added many decimal spots to the number for example 66.5 look like 66.5000000000001 when I click into the cell. I can not locate a pattern for why this is occurring (i.e. does not occur in the 4-8 or 10-15 but does in the 9 and 16 series). Formatting is set to numerical with 1 decimal point. Any help? I've jus...

Money Doubles up My downloaded transactions
Whenever money downloads my bank statements automatically, I always end up with duplicate transactions. I have money set up to not automatically accept transactions, but that doesn't seem to stop it. I spend at least 20 minutes a weekend sorting through the download duplicates to try and get my money balance to match my online bank balance. It is quite annoying. Anybody have any ideas how to fix this. Any ideas is microsoft will release an update? Thanks, Toby Tucker In microsoft.public.money, Toby Tucker wrote: >Whenever money downloads my bank statements automatically, I alway...

convert decimal hours to HMS?
Can someone please clue me as how to make this conversion? Thanks, E-Jo Divide the decimal hours by 24 and format the cell from hh:mm:ss. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Everett Joline" <ejoline@optonline.net> wrote in message news:%23n9gn5xfEHA.1356@TK2MSFTNGP09.phx.gbl... > Can someone please clue me as how to make this conversion? > Thanks, > E-Jo > > Hi E-Jo In general, divide by 24 and format as hh:mm. But "decimal hours" come in lots of flavors, like 1045, 10.45, 10....

Double Click Formula Cells
When you double click on a cell and there is a formula in the cell then the precendents are selected. Is there a way to turn this off or automatically go back to the cell you were on without any change to the window and its positioning. What is the VBA for this? Thanks in advance JJ Tools>Options>Edit Check "edit directly in cell" and you won't go to precedents. Application.EditDirectlyInCell = True Gord Dibben Excel MVP On Thu, 16 Sep 2004 13:34:07 +0100, "JJ" <f@o> wrote: >When you double click on a cell and there is a formula in the cell the...

Excel Hyperlink: Is there an easier way to follow then double-cli.
Excel Hyperlink: Is there an easier way to follow an Excel Hyperlink then with mouse double-click? My old version of Excel all you had to do was hit "Enter" In xl2002, I only have to use a single click. So that saves some time <bg>. This has been asked before and it looks like just selecting the cell and hitting enter is gone. Bob Small wrote: > > Excel Hyperlink: Is there an easier way to follow an Excel Hyperlink then > with mouse double-click? My old version of Excel all you had to do was hit > "Enter" -- Dave Peterson ...

Inventory
Hi, Altough we are using the currency in two decimal places. It does happen that certain inventory items, when purchased have a value that can go up to three decimal places. As the system only shows 2 decimal places for the cost, what happens to the third decimal place value. I hope the system just doesn't discard it away, or does it calculate it in the background??? Any ideas on what happens to this third decimal place. for example unit cost = .39 total quantity = 7.5 Total cost should be = 2.925 but the system shows = 2.93 what happens with this .005 cost. Sajid Saeed [Great P...

Bizarre
My company recently upgraded to 2003. Since the upgrade, I have been unable to open an excel file in any way except through the open file dialog in Excel itself. If I click on an icon (either on my desktop or an attachment in an email), I get a pause, a jpeg of an adobe icon gets copied into my clipboard and then a few seconds later, I get a message that the file can not be found. Everything else seems to work fine, all my models and files work fine, all my links work fine and I have virus scanned / spyware scanned left right and center. MY IT guys have given up and are now avoiding me. A...

make checks on the fly a double entry through accounts payable
with checks on the fly the step of posting to accounts payable is bypassed and this creates an out of balance issue between the cash account and the GL cash account ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/Ne...

double cash tray configuration
In a dual cash tray configuration, cashier log ins do not create/track individual cashier totals. Instead, they are lumped into one cash register total and both trays need to be counted and totaled together. An individual log in needs to be tracked separately. This would allow for two cashier log ins on the same machine giving the flexibility of and accountability of each cashier. One cashier could go to lunch or break or leave for the day without affecting the other cashiers tray. Flexibility and accountability would be improved significantly ---------------- This post is a sugg...

Excel Decimal Symbol
I need to produce spreadsheet files according to Continental and English rules relating to decimal symbol/digit grouping symbol. So, according to the end-users, I need to change the decimal symbol from comma to point and the digit grouping symbol from point to comma. Is it possible (e.g. by using custom number formats) to change the appearance of the numbers to reflect the different rules, without having to change the regional settings? Thanks, Excel is designed to automatically format numbers according to the user's Regional Settings. For instance, if I see this with my U.S. ...

Volatile UDF v ws_change conflict
Hi all In a worksheet there are cells that call an Application.Volatile UDF. The UDF does nothing other than return the rowheight of the cell that calls it. That worksheet also has a Worksheet_Change() event handler. If I delete the cells that call the UDF then the Worksheet_Change() routine works as planned. If I un-volatile the UDF then the Worksheet_Change() routine works as planned. But if I leave the UDF in the worksheet and leave it volatile (required) then the Worksheet_Change() event misbehaves. I shall post the full code of the UDF and the Worksheet_Change() if it would help, but I...

DPM 2007-Hyper-V
I am testing dDPM 2007. I am attempting to back up child partitions on a Hyper-V server. I get the error: DPM encountered a retryable VSS error. (ID: 30112) All my searches point to the need to apply hotfix KB959962, however, the hotfix indicates it is for Windows Vista, and, of course, cannot be installed. Any ideas? Please install Windows Server 2008 SP2 and KB971394 on your host machine. Even if it may say the update is for Windows Vista, please go ahead and download the update. After installing the update, please update the integration components on the virtual machine th...

Cell format decimals
When you format columns to 2 decimal places, it looks correct, but the cell value is still 2.543678093 for example. I remember there is a way to change this to just 2.54. I just can't remember how to do it. I'm not talking about cell format, there is another way to do this right? -- Ramthebuffs ------------------------------------------------------------------------ Ramthebuffs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16429 View this thread: http://www.excelforum.com/showthread.php?threadid=480870 =round(A1,2) Where A1 holds the decimal v...

Outlook 2002, Return Key Double Spaces
I am using Outlook 2002 SP2. Whenever I hit the return key it automaticly double spaces and I want to turn it off. Toddy <anonymous@discussions.microsoft.com> wrote: > I am using Outlook 2002 SP2. Whenever I hit the return > key it automaticly double spaces and I want to turn it off. Are you composing in HTML format? If so, hold the Shift key when pressing Enter. In HTML, Enter means "new paragraph" while Shift-Enter means "new line". -- Brian Tillman ...

Decimal to Minutes
Is there a format or formula to convert a decimal to time? An example would be .5 would be 30 (minutes), .75 would be 45 (minutes), etc. The Workbook is relatively large so any method would have to easily apply to thousands of numbers, versus simply manually converting one cell at a time. Regards, Gary It's easier than you think Gary, since 1 is one day in Excel which is 24 hours, so one hour is 1/24 just divide by 24 =A1/24 where A1 holds 0.5 then the important thing is to format the cell with the formula as either hh:mm:ss or [hh}mm:ss (the latter allows for displaying...

getxml() function on dataset: problem with decimal places
I am working on an ASP.NET application, developed with the 1.0 framework. After upgrading to the 1.1 framework I have noticed a difference in the way the GetXML method returns decimal data from a dataset. Previously, when a column defined as decimal with scale 3 in SQL server 2000 contained a number with no fractional part (say 4000), GetXml applied to a dataset read from the table would return a string without separator or decimal numbers ("4000"). Now I get a string which contains the decimal separator and zeros equivalent to the scale defined for the column ("4000.000")....

Outlook XP exporting into Entourage v.X
Is there anyway to import directly from Outlook XP into the new Entourage v.X? The only outlook that says is capable of direct import is Outlook Express 5. This makes no sense. Any assistance would be appreciated. Thank you. ...

file size sometimes doubles when changing one or two cells
Hi, i have a very strange problem with excel2000. When i change one or two cells in a sheet containing links, the file size doubles or triples in size? This is only the case when working in a terminal server environment. When working with the same files in a normal environment (pc + network drive), this is not the case. Can this be a wrong setting in excel2000 on the terminal server environment? Any help or hints are very welcome. Thanks Wim ...

Anyone know A/V software that can block attachments for only SPECIFIED users?
I am running an AD 2003/EX 2003 network. Are there any anti-virus programs out there that I can set attachment blocking based only on certain users? For example... I would like to block all incoming/outgoing ZIP files for everybody... except for my three developers which need them on a daily basis. Any way to do that with any program? Thanks. RICH ClearSwift's product called MailSweeper is very good in this regard. We use it to do exactly what you mention. Cheers "Richard Treleaven" <djpill@ruderfinn.com> wrote in message news:66222c03.0402251110.1dfa1735@posting.goog...

decimal format on the form
Hi, I was able to set the format on the form. I am creating inbound box like: ="Total Average Headcount: " & [Count] I'm getting the result. "Total Average Headcount: 3222.87777". Is there a way to change like this "Total Average Headcount: 3223" Your help would be much appreciated. Thanks. Try... = "Total Average Headcount: " & Format([Count],"#") -- hth Al Campagna . Candia Computer Consulting . Candia, NH USA Microsoft Access MVP http://home.comcast.net/~cccsolutions "Find a job that you love, and you'l...

general fromat goes to 4 place decimals
Numbers entered in the general format (not numbers format) go out to 4 place decimals even when entered with-out decimals. Any clue? Is it a system setting thing? .. They aren't also 10,000 times smaller than they should be are they?? OIf so, then Tools / Options / Edit / Uncheck 'Fixed Decimals' -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- Newsgroups - Where you really can get a free lunch!! ----------------------...

Integer and Decimal
Hello: Using Excel 2002 (XP) I know how to remove the Integer of 14.67 and end up with .67. But how can one remove the decimal portion and leave the existing Integer 14? Thanks, Mark One way: =INT(14.67) In article <uF2MpvxbHHA.5044@TK2MSFTNGP05.phx.gbl>, "MarkC" <None@nospam.com> wrote: > Hello: > > Using Excel 2002 (XP) > > I know how to remove the Integer of 14.67 and end up with .67. But how can > one remove the decimal portion and leave the existing Integer 14? > > Thanks, > > Mark Thanks JE!!! MC "JE McGim...

Getting double entries
I'm using Outlook Express ( I know I could do better, but no time, and I did look for an OE newsgroup, didn't find)) I'm getting double entries of some, but not all incoming emails. I used to know how to correct, but don't remember how. Can anyone help, please? Sylvia M. Sylvia M wrote: > I'm using Outlook Express > ( I know I could do better, but no time, and I did look for an OE > newsgroup, didn't find)) > I'm getting double entries of some, but not all incoming emails. > I used to know how to correct, but don't remember ho...

eConnect v 8.0.4 with SQL Server 2005
I have GP version 8 and eConnect v 8.0.4 installed. It’s working fine in SQL Server 2000. Now, we have upgraded from SQL Server 2000 to 2005. We are using only INCOMING Service; I like to know whether any conflicts will occur. Please clarify. Thanks in advance. ...

Lost decimal precision for single and double number format-Access2
I ran an update query to find certain values in a table and replace them with: -9999.999999 for my single format columns (6 decimal places) -9999.99999999999999 for my double format columns (14 decimal places) Access presented a message saying it had done so but when I look at the table, the updated values are -10,000. If I repeat the update query to replace -10,000 with the above values, Access presents a message saying it will replace the same number of rows as before with the above values, meaning that it really is -10,000 that it updated to the first time. Why won't it give me ...