# 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
```

` ` 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)

#### Related Posts

• Shrikant
December 24, 2014 4:30 pm

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

• Mahesh
December 24, 2014 4:32 pm

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

• anilkumarup
December 24, 2014 4:38 pm

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.

• SenthilPrabhu
December 24, 2014 4:42 pm

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

• anilkumarup
December 24, 2014 4:51 pm

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.

• Rushik
December 24, 2014 5:15 pm

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

• bhushanmehetre
December 24, 2014 6:57 pm

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

• Ganesh
December 24, 2014 7:13 pm

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

• Bala
December 24, 2014 9:56 pm

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

• Maheswar
December 25, 2014 8:13 am

int/int=int,int/decimal||decimal/int=decimal

• Maulik Patel
December 25, 2014 11:35 am

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

Thanks

• 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

• Naresh
December 26, 2014 12:49 pm

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.

• Rajagopal
December 26, 2014 1:03 pm

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

• Lokesh
December 26, 2014 4:14 pm

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

• Ankit K Goyal
December 26, 2014 4:17 pm

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.

• anji
December 27, 2014 11:33 am

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.

• Thank you all – great answers.

I will announce the winner in my newsletter.

• manifbest
December 29, 2014 11:09 am

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.

• Dave Merton
December 29, 2014 7:23 pm

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]