Just like a week, during a user group meeting I was told to ask questions about why the SELECT 1 /2 gives a different answer then one user is expected. Let us see this question in detail about SELECT One by Two as it can be a great learning for many new beginners.
Question: Why does SELECT 1/2 returns 0 in SQL Server?
Answer: Well, if you think the answer of 1/2 should be 0.5. However, when you execute the same code in SQL Server Management Studio, you get the answer which is 0.
Here is the script which you should run on SQL Server Management Studio.
SELECT 1/2;
Every single time when I have asked this question to user who are just beginning with the SQL, their reaction is that it is a bug. Well, common sense definitely says this is a bug but just like in real life the rules of love, war and jungle are different, for SQL Server it is a different case as well.
Well, the answer is very simple – it is all about datatype of the resultset. The rule for division in SQL Server is that it will look at the parameters and based on the their datatype, it will assume the datatype of the resultset.
Now in our case the datatype of numerator and denominator both are integer, hence the resultset has a datatype of integers. When 1/2 is calculated arithmetically, SQL Server does internally knows that the answer is 0.5. Later on when it has to display the answer, it will covert 0.5 to integer and result is displayed as a 0 (zero).
Let us validate our assumption by running the following script:
SELECT 1/2.0
You will find that it will return output as a 0.500000. This is because now numerator is an integer, but the denominator is float and SQL Server will now have to assume the resultset in the data type float. Internally SQL Server converts the datatype of the integer to float and does the division operation.
Here is the link which can be helpful for you to understand how the implicit conversion happens. Additionally, to understand which datatype is takes precedence over another datatype, I suggest you read this article as well.
Here is my earlier blog post which I strongly recommend to read:Â Simple Explanation of Data Type Precedence. Let me know what you think of this SELECT One by Two problem.
Reference: Pinal Dave (https://blog.sqlauthority.com)
22 Comments. Leave new
Hi Pinal
Thanks for sharing your knowledge. I want to know that when we try to convert whole calculation explicitly into float then why it does not work?
SELECT CAST((1/2) AS FLOAT) — This is not working. It will give result as 0.
If I try will to convert it like below manner i.e. individually then it will give correct result as 0.5.
SELECT CAST(1 as FLOAT)/CAST(2 as FLOAT)
SELECT CAST(1 AS FLOAT)/2
SELECT 1/CAST(2 AS FLOAT)
Any help would be grateful.
Warm Regards
Anuj Soni
Dear Anuj Soni,
I think it’s about the execution or evaluation order of the expression.
At first the tsql interpreter evaluates the calculation inside: 1/2 = 0.
And just after it casts this result.
Before converting compiler parses 1/2 then converts to FLOAT hence it still returns 0
Anuj, You are converting to float after the result is produced (1/2 is 0) hence conversion of float is 0. You need to convert one of the values into float before division
SELECT cast(1 as float)/2
Now the result is 0.5
Hi Anuj,
This is what you are expecting?
select convert(float,1/2.0 )
Why doesn’t SQL round up if the resultset is an integer? 0.5 would round up to 1 in most cases except this one.
SELECT 1.0/2 works too.
@Anuj, your cast wont work because the implicit conversion has already happened before. You can see the latter three statements enforce the cast before division.
SELECT 1./2
SELECT 1/2.
@John – Good question. It is because SQL isn’t rounding, it is using the integer part of the answer. Try SELECT 2/3 or SELECT 99/100
Awesome TIP
Pinal, please keep educating the community and appreciate your volunteership.
Whoever sees this TIP will definitely say lesson learned for the day
I await some such knowledge
Note nobody can steel individuals knowledge
Here is similar post on Beware of Implicit conversion
the same:
select cast(0.5 as int)
Thanks Guys for your valuable feedback.
Yep. I understood the process why it was not coming 0.5 and instead shows 0 because it does division before conversion.
when you write the statement SELECT 1/2, SQL Server recognizes the numbers 1 and 2 as integers. Since the value 1/2 is .5, this is not an integer. SQL Server uses a specific rounding method, which results in Zero. If you instead wrote the command as SELECT 1. / 2. you would get the more accurate result of .5. This is because 1. and 2. are implicitly assigned a float data type, if memory serves me right. Regardless, they are not integers, and decimal math is then available.
Feel free to post my web site as www. sqlsageadvice.com if I meet your requirements.
Ben
@Ben – If you want to build up a following you need to put in an immense effort like Pinal. I see from your site that the last post was 18 months ago. The specific rounding method you mention above rounding down, or floor-ing. Also you suggest that zero is not an accurate result, but it is. Just because the expectation was to see a floating point answer, doesn’t make the integer one wrong.
Dividing 1/2 in most regular coding always gives the answer of 0 for the same reason SQL Server does so. It blew my mind when I was learning VB.NET that the answer was “1”, then I found out that you have to do 1\2 to get a truncated down answer.
@Ken – Not quite true. In the world of VB.NET
1/2 = 1.5 because integers are automatically converted to doubles prior to division
12 = 0 because the remainder is discarded (ie rounding down) https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/integer-division-operator
Oops. Should have read 1/2 = 0.5 ;-)
Awesome question
Not everybody knows this functionality of SQL rule
Pinal please keep publishing such interesting articles.
I enjoyed reading this article and recommend others.
Thanks for educating the community and appreciate your volunteership
If you are dealing with columns is col1/(1.0*col2)
That’s not a float data type, but it’s a numeric data type. Please correct this article, thank you.