The puzzles are interesting and in most cases, if we try to play around a little bit more, many more interesting extensions can be added. A couple of weeks back, I wrote a round – SELECT One by Two – Why Does SELECT 1/2 Returns 0 – Interview Question of the Week #067. It brought some good interactions as comments over the blog and I was happy we were all excited about something as simple as this.
There were tons of learnings that cannot be denied. A lot of you were giving me options of converting the values or cast the values and many more options have been explained in the comments section of that blog. I would like to refrain from rehashing the same. But keep those comments flowing – we get to learn more from each other.
Now I went ahead to play a small extension to the same query. See the 3 queries written below:
-- Query 1 SELECT 1.*1/2*1. "Query 1" GO -- Query 2 SELECT 1.*1/2 "Query 2" GO -- Query 1 SELECT 1/2*1. "Query 3" GO
Do you think there is anything different on these 3 queries? Shouldn’t they return the same results? My first thought was to get the same results. But there will always be surprises to learn from.
Trust me, I have not done any manipulation here and the query results have been done as-is from my SQL Server Management Studio window. I have made the problem statement simple. Why am I getting a different output? What have I don’t differently? Why is SQL Server behaving like this? Is this the expected output?
Note: Part of the solution is very much available in the first post. Do make sure to read the post and the comments carefully and do share your understanding / reason for the above output. Let us learn together again.
Reference: Pinal Dave (https://blog.sqlauthority.com)