Aim :- This article aims at a small yet interesting question i.e. what will be the output of the given statement in SQL Server – (Select one by two ( ½) )?
Description :- This looks like an easy question with a straight answer. One of my friend asked me this question. He faced this scenario in his recent interview. When he came back to room, he tried to test my knowledge base and asked me the same question. Immediately I replied him with an answer as “0.5”. I then laughed and asked him to ask me tough questions in future. He told me that my answer is wrong. Also, he suggested me to execute this Select statement in the SQL Server to know the answer. Anxiously, after listening to his words, I opened my system and executed the Select statement in my SQL Server machine. Any guesses, what can be the output of this statement?
I expected 0.5 but it has given zero (0). I was really surprised to see why the answer is Zero (0) and why not 0.5? SQL Server doesn’t agree with my opinion. Ah! My guess was wrong. Let’s see this scenario into depth. Consider two approaches mentioned below –
- Mathematical approach
- Sql server approach
Mathematical approach : “1/2 = 0.5”
According to Arithmetical calculations, answer of 1 divided by 2 is equal to 0.5. This was my approach while answering the question. But, we can see in SQL Server that this is a wrong answer because output showed zero (0). One suggestion to all of you is to think twice before you give any answer to somebody. Sometimes, question looks simple but in reality is tricky. In this case, if we apply normal mathematics, then answer to select one by two (1/2) is 0.5. This proves my answer was correct according to mathematical approach.
Sql server Approach : “1/2 = 0”
In SQL Server, we didn’t get appropriate answer when we issue the given Select statement. Let’s see practically what answer SQL Server gives us when we issue the Select one by two (½) statement.
So, we get the answer equal to Zero (0). Why it has given the answer as zero and why not 0.5?
The reason behind this is that 1, 2 are considered as integers in SQL Server. So, when we issue a select statement like above by default it will consider those values as integers. This is likely because SQL server is using integer arithmetic for the calculation. The integer part of ½ is 0. This is the only reason for the output to be 0. But this is a wrong answer
So, now we have to think how we can move ahead with correct approach. Following are the different approaches to achieve the correct answer.
I hope you all are aware of “Cast and Convert “functions. Here, I am going to use both the functions in the following scenarios. If anybody doesn’t have idea about these two functions, here you go http://msdn.microsoft.com/en-IN/library/ms187928.aspx
With this we came to the end of this interesting article. I hope this article on select one by two (1/2) in SQL Server helped you all to learn Cast and Convert functions. If you liked our effort, kindly mention below in comments.