SQL SERVER – Puzzle – Why Decimal is Rounded Up?

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


SQL SERVER - Puzzle - Why Decimal is Rounded Up? decround1

Here is the second script:

-- Decimal is not rounded up
DECLARE @MyValue DECIMAL(10,2)
SET @MyValue = 10000/17.0000
SELECT @MyValue DecResult
GO

SQL SERVER - Puzzle - Why Decimal is Rounded Up? decround2

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)

Previous Post
SQL SERVER – Story of Temporary Objects
Next Post
SQL Authority News – I am Participating in Winter Discounts and Christmas Photo Fest

Related Posts

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

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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)).

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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….

    Reply
  • 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

    Reply
  • The NUMERATORS or DENOMINATORS are decimal numbers rounded up is not work other wise rounded up is work well.

    Reply
  • int/int=int,int/decimal||decimal/int=decimal

    Reply
  • int/float in second script that’s why it’s not round up.

    Thanks

    Reply
  • Dilip Kumar Singh
    December 26, 2014 11:56 am

    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

    Reply
  • 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.

    Reply
  • 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 ;”

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • Thank you all – great answers.

    I will announce the winner in my newsletter.

    Reply
  • 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.

    Reply
  • 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]

    Reply

Leave a Reply

Menu