Lots of people know the answer to this puzzle, but I am sure there are many who do not know the answer of the same as well. Here is the puzzle again for those who have vacation for the Holidays!
Here is the first script:
-- Decimal is rounded up DECLARE @MyValue DECIMAL(10,2) SET @MyValue = 10000/17 SELECT @MyValue DecResult GO
Here is the second script:
-- Decimal is not rounded up DECLARE @MyValue DECIMAL(10,2) SET @MyValue = 10000/17.0000 SELECT @MyValue DecResult GO
Here is the question –Â When we execute the first script the decimal’s are rounded up and when we execute the second script the decimal’s are not rounded up. WHY?
Just leave your answer in the comments field. I will select two random winners in the first week of January and will send surprise gift worth USD 29.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
48 Comments. Leave new
yes.
Case 1 :
As both value are integer So it will divide two integer number.Then it will be converted to
decimal (10,2) .So we are not getting rounding value.
Case 2:
As One of the value is decimal So it will divide Integer/Decimal , output will be decimal .Then it will be converted to decimal (10,2) .So we are getting rounding value.
Happy Christmas – Pinal
10000/17 : In the first script both the divisor and dividend are int. so the result datatype will be integer and explicitly we are moving the result to the variable of datatype decimal.
10000/17.0000 – 588.2352941 : In the second script the divisor is NUMERIC and dividend is int. so the result datatype will be based on the argument with higher precedence. Here Numeric datatype is having higher precedence than int. Hence the result datatype is NUMERIC (588.2352941). we are moving the result to the variable of decimal datatype(10,2). So the final result will be rounded to 588.24
This not about type of Variable to which output will be assigned, its about the operand’s data type. in First example both are integer therefore output is integer and similarly in second, one of the operands is decimal so output is decimal.
Lets begin with basic data types:
1) Exact numerics { tinyint, smallint, int, bigint, numeric & decimal (where numeric and decimal allows precision and scale)
2) Approximate numerics {float, real}
3) Date and time
4) Binary strings
5) Character strings
6) Unicode character strings
7) Other data types
In the above example first part happens is computation now SQL Server has to decide which data type must be picked.(Smartly picks the biggest data type) now computed result is send to the variable. where an implicit conversion occurs
NOTE: SQL Server automatically converts the data from one data type to another. For example, if a smallint is compared to an int, the smallint is implicitly converted to int before the comparison/Operation proceeds
When the resultant data is sent to the variable(@MyValue) again it will check possibility for implicit conversion of the data to the variable type(DECIMAL(10,2)).
Why int/int is an int? When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.
Hello Sir,
We need to cast one of the denominator or numerator as a decimal to get decimal output.
In First Case, we have both denominator and numerator as integer datatype, so SQL server while performing division will create a integer value and assign it to decimal variable which will implicitly convert integer to decimal and add “0” as the fraction values.
In Second Case, we are passing denominator as a decimal value, so SQL Server while performing division will implicitly cast the numerator as a decimal and will create decimal output and assign it back to the decimal variable.
Thanks,
Rushik
It is showing the result differently because result of (10000/17) and (10000/17.00) are different.
Lets see one by one
For the first script:-
If we make the division of two int number (no without decimal points) then it will give you the result in int only.
so 10000/17 will return 588 but @MyValue is the decimal type variable so it is casting the int value to decimal implicitly and append ’00’ at the end.
So @MyValue shows the value in select as “588.00”.
For the second script:-
If we make the division of two number one is int (without decimal points) and second of data-type of int then it will give you the result in decimal only.
so 10000/17.00 will return 588.235294 but @MyValue is the decimal type variable so it will convert 588.235294 to 588.24 because @MyValue data-type declaration for the decimal point up to 2.
and it shows the value in select as “588.24”.
This is what I think. Please correct me if I am wrong.
Suggestions are always welcome….
when we do division of 2 int then result will be int only.. as you do division of 1 int and 1float then result should be float/decimal
The NUMERATORS or DENOMINATORS are decimal numbers rounded up is not work other wise rounded up is work well.
int/int=int,int/decimal||decimal/int=decimal
int/float in second script that’s why it’s not round up.
Thanks
Hi Pinal,
By default, SQL Server uses rounding according to followed data type.
In first script same things happened, It’s treat 10000/17 as int data type and round according int then set value in “@MyValue” variable
Below script can explain it
DECLARE @MyValue DECIMAL(10,2)
DECLARE @MyValue1 INT=10000
DECLARE @MyValue2 INT=17
SET @MyValue = @MyValue1/@MyValue2
SELECT @MyValue
Result
588.00
DECLARE @MyValue DECIMAL(10,2)
DECLARE @MyValue1 DECIMAL(10,2)=10000
DECLARE @MyValue2 DECIMAL(10,2)=17
SET @MyValue = @MyValue1/@MyValue2
SELECT @MyValue
Result
588.24
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000/17
SELECT @MyValue DecResult
GO
O/P:588.00
Reason :here 10000 and 17 both are the integer values,so o/p will be integer and this will convert to decimal then o/p=588.00.
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000/17.00
SELECT @MyValue DecResult
GO
o/p:588.24
Reason :here 10000 is an integer data type and 17 is an float data type so o/p will be float because float is the bigger data type then the integer datatype and this will convert to decimal then o/p=588.24.
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000/17
SELECT @MyValue DecResult
GO
1) In this query 10000(int)/17(int) is return int value(588), then int values is implicitly convert to decimal values(588.00) because int is no decimal point but
2) How to process means
For Example :
decimal = int = 10000(int)/17(int)
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000/17.0000
SELECT @MyValue DecResult
GO
1 ) In this query 10000/17.0000 is return decimal because 10000 is int and 17.0000 is decimal but highest datatype size is decimal so that it return decimal and decimal value is convert decimal their is no change so that is return 588.24
2) How to process means
For Example :
decimal = decimal = 10000(int)/17.0000(decimal) –highest datatype size is decimal
int = 4 or bigint = 8
decimal = 17 , numeric = 17 , float = 8 ,real = 4
refer “select name,max_length from sys.types ;”
In first case the result of division operation will be rounded off to integer because numerator and denominator are integers, however on storing it inside decimal type will append zeroes at the end.
However In second case result will be float because denominator is float. So no rounding off will occur because the result will be of float type
Hi,
Storage size for
INT = 4 bytes
FLOAT = 4 or 8 bytes (depends on number of bits)
DECIMAL = 5-17 bytes
Now, in case of 10000/17, both Numerator and Denominator are int (4 bytes) value so result is also an integer.
but in case of 10000/17.00, Denominator is a float value, so result is generated in same type.
More storage size of float, save more space in memory for resultant.
When a number divide with integer, result will come in integer format so value rounded up. In other case, number is divided by float value so not rounded up.
Here , if any one of the number either NUMERATOR or DENOMINATOR is decimal , the result is in decimal format or it will not rounded up.
RE: SELECT 10000/(17/17/17) AS Result2;
1. The first 17/17 evaluates to 1
2. 1/17 evaluates to zero! (This is because they are both int datatypes)
3. 10000/0 will always cause an error.
In order to have this not crash you could do something like this:
SELECT 10000/(17/17.0/17) AS Result2, hoever the result will look a little strange.
-Dave Merton
[email removed]
By default SQL will understand numeric value as integer.
In First scenario, 10000/17 means [Int/Int] and result is 588. But it assigned to decimal variable so value will display 588.00.
In Second scenario, 10000/17 means [Int/Decimal] so result will come in decimal and value will be 588.24. same value will assign to decimal variable and display 588.24 value in result.