sql server - decimal vs real datatypes

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
troyvwAsked:
Who is Participating?
 
Lara FConnect With a Mentor EACommented:
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
 
troyvwAuthor Commented:
if i change the real to decimal(38, 0) I would have 38 total characeters correct?
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Lara FEACommented:
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
 
troyvwAuthor Commented:
It would round to the nearest what?
0
 
troyvwAuthor Commented:
What decimal should i used then?
0
 
troyvwAuthor Commented:
I want to have some decimal places
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
troyvwAuthor Commented:
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
 
troyvwAuthor Commented:
OK i undestand now. thank you
0
 
Lara FEACommented:
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
 
troyvwAuthor Commented:
OK thank you
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Scott PletcherSenior DBACommented:
>> 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
 
troyvwAuthor Commented:
ty
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.