Rayne
asked on
decimel help
Hello All,
I have a table where a column is gets updated by the division of a column from another table by any denominator value between 3 and 9. So it could be metric column / 3 or metriColumn/4 , etc.
Now in terms of the maximum of decimals for highest accuracy – s – what should be used? Wen creating that table to store the division result column – I am using –
CREATE TABLE [dbo].[myTable](
[divisionResults] [decimal](18, 4) NULL
) ON [PRIMARY]
I want to use storage type that allows me the maximum decimal places after zero but also doesn’t compromise the query efficiency or speed or that I am “overkilling” it
I have a table where a column is gets updated by the division of a column from another table by any denominator value between 3 and 9. So it could be metric column / 3 or metriColumn/4 , etc.
Now in terms of the maximum of decimals for highest accuracy – s – what should be used? Wen creating that table to store the division result column – I am using –
CREATE TABLE [dbo].[myTable](
[divisionResults] [decimal](18, 4) NULL
) ON [PRIMARY]
I want to use storage type that allows me the maximum decimal places after zero but also doesn’t compromise the query efficiency or speed or that I am “overkilling” it
You can see the precision for decimal data type here and as far as I know the precision doesn't impact the query performance.
One additional thing to rember is when you do math in sql with an int and a decimal, depending on the specific order of the numbers (decimal/int , int/decimal) you can get the result as an int when you want a decimal.
https://www.experts-exchange.com/questions/24432624/How-do-I-divide-two-integer-values-and-get-a-result-that-includes-2-decimal-places.html
I prefer to explicitly cast my int as a decimal but there are lots of ways.
Decimal / cast (int as decimal(18,4)
https://www.experts-exchange.com/questions/24432624/How-do-I-divide-two-integer-values-and-get-a-result-that-includes-2-decimal-places.html
I prefer to explicitly cast my int as a decimal but there are lots of ways.
Decimal / cast (int as decimal(18,4)
depending on the specific order of the numbers (decimal/int , int/decimal) you can get the result as an int when you want a decimal.That's not true. In both case the result will be always a decimal.
Only if the operation is int/int that the result will be int.
whoops, that's right. I've just gotten in the habit of casting everything to avoid odd issues.
http://improve.dk/tricky-sql-server-decimal-math/
http://improve.dk/tricky-sql-server-decimal-math/
ASKER
[myResults] [decimal](18, 4) NULL
so whats the max number of decimel that i can use? instead of 4 like up till 12 or more?
so whats the max number of decimel that i can use? instead of 4 like up till 12 or more?
ASKER
please suggest a valid one - this is leading to arithmetic overflow
ASKER
all eexperts - please suggest whats your best practise of using which one decimel ? i dont want my program to crash
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Sire Mike and the other Gurus, thank you
"Sire Mike" !! LOL!!!
I appreciate the thought. -- Mike
I appreciate the thought. -- Mike