Division: SQL Server Style

When doing calculations in SQL Server queries it is important to know that anything without a decimal is going to be treated as an integer and therefore will cause the result to be an integer as well.  For instance, lets take 598/128 and look at some results.  This will obviously give us a decimal for a result…or at least it SHOULD.

SELECT 598/128 = 4

This is clearly NOT an even 4, however SQL has decided to round the result even without us telling it to.

Now lets take a look at SELECT CONVERT(DECIMAL(7,5),598/128).  Strangely enough, that also evaluates to 4.

If you want to force SQL to return the correct result, in this case a decimal, you need to simply add a decimal to any number in the expression.  This will force the result to be evaluated and displayed as a decimal value, not a rounded integer.  The following, just adding a decimal after 598 now brings back the correct result: SELECT 598.0/128 = 4.61745

Try running each of these three select statements on your computer to see the differece between the outputs.  And remember, if you want precision in your result, not rounding, just add a decimal placeholder.

SELECT 598/128
SELECT CONVERT(NUMERIC,598)/128
SELECT 598.0/128
SELECT 598./128

Bradley Schacht

Bradley Schacht is a Cloud Solution Architect on the state and local government team with Microsoft based in Jacksonville, FL. He has co-authored 3 SQL Server books including "SQL Server 2014 Professional Administration". As a former consultant and trainer, he uses his experience on many parts of the Microsoft BI and data platform to help customers deliver the best possible solutions. Bradley frequently presents at community events around the country. He is a contributor to sites such as SQLServerCentral.com and an active member of the Jacksonville SQL Server User Group (JSSUG).

You may also like...

Leave a Reply