Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql server - decimal vs real datatypes

Posted on 2010-09-14
16
Medium Priority
?
743 Views
Last Modified: 2012-05-10
How many decimal places for the decimal vs real datatypes? what would happen if i updated a real datatype to decimal.

some of the values are like this for example:

0.879999995231628
0
Comment
Question by:troyvw
  • 8
  • 3
  • 3
  • +1
16 Comments
 
LVL 11

Accepted Solution

by:
Larissa T earned 1000 total points
ID: 33676805
in decimal definition decimal(N,M)
m will specify number of digit after decimal point, N total number of positions, including decimal point

depending on you decimal definition this it can be truncated

declare @dec decimal(19,2),      @real float
set @real=0.879999995231628
set @dec=@real
select @dec



0
 

Author Comment

by:troyvw
ID: 33676843
if i change the real to decimal(38, 0) I would have 38 total characeters correct?
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 33676855
If you convert real to decimal, SQL will round as necessary to meet the new definition.

You only need real for **extremely** large or **extremely** small numbers or for lengthy involved computations.  Otherwise use decimal, since it's much more exact and easier to use.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:Larissa T
ID: 33676886
decimal(38,0)  means no decimal point. You real number will be converted to 1
declare @dec decimal(38,0),      @real real

set @real=0.879999995231628
set @dec=@real
select @dec
0
 

Author Comment

by:troyvw
ID: 33676887
It would round to the nearest what?
0
 

Author Comment

by:troyvw
ID: 33676903
What decimal should i used then?
0
 

Author Comment

by:troyvw
ID: 33676910
I want to have some decimal places
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33676931
A real datatype like any other float is very imprecise.  If your data requires precise values, changing to the decimal is the right way to go.
http://msdn.microsoft.com/en-us/library/ms173773.aspx

Now it seems your question is the right Decimal value to use.  The question there would come in what is the number of decimal places you want to go out to and do you expect to get a equally long whole number.

0.879999995231628

If the above represents your target data and have no whole number values, you could use:

decimal(15, 15)

Alternatively you could go decimal(30, 15) or decimal(38, 19) and split down the middle to accept :
879999995231628.0
or
0.879999995231628
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33676952
Arg. I guess I should have said "ditto" since Scott said the same thing here http:#a33676855

To exemplify Scott's point:
declare @real real
set @real = 0.879999995231628;
select convert(decimal(38, 10), @real);
-- results in 0.8799999952 << rounded to 10th decimal place
0
 

Author Comment

by:troyvw
ID: 33676963
so that i undertand

decimal(15, 15)


would allow 15 to the left and 15 to the right of the decimal point?

decimal(13, 17)

would allow 13  to the left and 17 to the right of the decimal point?
0
 

Author Comment

by:troyvw
ID: 33676989
OK i undestand now. thank you
0
 
LVL 11

Expert Comment

by:Larissa T
ID: 33677008
decimal(N,M)
M will specify number of digit after decimal point, N total number of digits

decimal(13, 17) is invalid
The scale must be less than or equal to the precision.

decimal(15, 15)
will have only 15 position AFTER decimal point


0
 

Author Comment

by:troyvw
ID: 33677018
OK thank you
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33677051
Yes, agree with previous Expert.  To allow 15 to left and right, you will need decimal(30, 15) as I said in my post.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 33679064
>> It would round to the nearest what? <<

As I stated, "SQL will round as necessary to meet the new definition".

So however many decimal places are in the new column data type, SQL will automatically round to that.


0
 

Author Closing Comment

by:troyvw
ID: 33691601
ty
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question