SELECT One by Two – Why Does SELECT 1/2 Returns 0 – Interview Question of the Week #067

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)