• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

Scale and precision in SQL 2008

I am trying to build a script that will distirbute large numbers over a range distrinbuted values: 1,000,000 distributed over 7000 recorfds with varying ratio. So far, I hav eonly been able to scale out to 6 on the presicion. I would liek the scale to be at least 10. I have been using decimal18,10, but it still caps at 6. Is there any thjing I cna do to tfix this? It sthrough my disotribution off by a few points.
0
jlhmead
Asked:
jlhmead
  • 4
  • 3
1 Solution
 
ralmadaCommented:
decimal has a maximum precision of 38 so I'm not sure what you mean that it still caps at 6. Can you please clarify what are you trying to do? In the mean time did you try with more precision decimal(24,10) for instance?
0
 
ralmadaCommented:
0
 
jlhmeadAuthor Commented:
It was only display 6 decimal places. I reslised the denomiator drive the scale so I had to make sure that al the fields that were invloved in the calculation were set at decimal 18,10. That did the trick.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
ralmadaCommented:
absolutely, just so you know in SQL if you divide integers you will get integers    5 / 2 = 2 not 2.5

If you want decimals can simply multiply by "1.0"  for instance 5 * 1.0 / 2 = 2.5 that will do an implicit conversion.
0
 
jlhmeadAuthor Commented:
An example: 1500/7615827.14 should return a value of 0.000196958

What I am seeing on the file is a value of 0.000196000

I did the first calculation in excel

Any ideas why this is happeing?

I am using decimal(18,10)

0
 
ralmadaCommented:
you might try cast(1500 as decimal(18,10)) / cast(7615827.14 as decimal(18,10)) to be 100% sure that both are decimals. But also you might consider converting to float. Although float are not precise numbers, so you might have some discrepancies...

0
 
jlhmeadAuthor Commented:
That worked so thanks.
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now