Division operator in SQL Server returns 0 always

This is very small thing even I tempted to write about this topic because I have found this kind of questions in several forums. This is just a lack understanding. Suppose I have some value to calculate like (2/10)*100, what should be the answer??? Of course, it’s very simple, even primary school students can answer it. Answer is 20.

Now run following query in your SQL Server’s query analyzer.

select ((2/10)*100)

It will return 0 rather than 20. Why? Is there any problem or bug in SQL Server for division or multiplication operator? Answer is NO. There is no bug you are trying to divide the integer value with integer so 2/10 is 0.20 and it will be rounded up into integer and become 0.

So, here is the reason but how to solve it. There are few ways to do so.

Either covert your 2 to double or multiply 2 with some double value then SQL Server engine will take care of rest of the task.

Run following query and you will get perfect result.

--multyplying our integer value 2 with float value 1.0 to convert int 2 to decimal 2

select ((2*1.0)/10)*100

--or

--used convert function to change integer to decimal

select (convert(decimal(5,2),2)/10*100)

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com