Numbers: integer division, rounding and decimal precision

SQL Server does arithmetic in the types of its operands. Two ints give an int; mix in a decimal and you get a decimal with a precision and scale worked out by rules. Most surprises in calculations come from those two facts.

Integer division and remainder

Screws come in boxes of 200. How many full boxes is 1,050 screws, and how many are left over?

1SELECT 1050 / 200 AS FullBoxes,2       1050 % 200 AS LeftOver,3       1050 / 200.0 AS ExactBoxes,4       CEILING(1050 / 200.0) AS BoxesToBuy;

FullBoxes  LeftOver  ExactBoxes  BoxesToBuy
---------  --------  ----------  ----------
        5        50    5.250000           6

With two ints, / gives the whole part and % the remainder. Writing 200.0 makes it a decimal, so the division keeps its fraction. CEILING then rounds up, because you cannot buy a quarter of a box.

Rounding

ROUND keeps the original type, so its result still has the same number of decimal places, filled with zeros. To store or show a price with exactly two places, cast it:

1SELECT Name, Price,2       Price * 1.2 AS RawIncVat,3       ROUND(Price * 1.2, 2) AS Rounded,4       CAST(Price * 1.2 AS decimal(10,2)) AS IncVat5FROM dbo.Products6WHERE CategoryId = 1;

Name              Price  RawIncVat  Rounded  IncVat
----------------  -----  ---------  -------  ------
Claw hammer       18.50     22.200   22.200   22.20
Cordless drill    89.00    106.800  106.800  106.80
Tape measure 8 m   9.75     11.700   11.700   11.70

What type does a calculation return?

For decimals, SQL Server works out the result’s precision (total digits) and scale (digits after the point) from the operands. Multiplying adds the scales; dividing gives at least 6 decimal places. You can ask SQL Server what it chose:

1SELECT SQL_VARIANT_PROPERTY(CAST(10 AS decimal(10,2)) * CAST(3 AS decimal(10,2)), 'Scale') AS MultiplyScale,2       SQL_VARIANT_PROPERTY(CAST(10 AS decimal(10,2)) / CAST(3 AS decimal(10,2)), 'Scale') AS DivideScale,3       CAST(10 AS decimal(10,2)) / CAST(3 AS decimal(10,2)) AS Quotient;

MultiplyScale  DivideScale         Quotient
-------------  -----------  ---------------
4              13           3.3333333333333

Line totals and percentages

1SELECT OrderId, ProductId, Quantity, UnitPrice,2       Quantity * UnitPrice AS LineTotal,3       Quantity * 100 / 40 AS PctOf40Int,4       Quantity * 100.0 / 40 AS PctOf405FROM dbo.OrderItems6WHERE OrderId = 4;

OrderId  ProductId  Quantity  UnitPrice  LineTotal  PctOf40Int     PctOf40
-------  ---------  --------  ---------  ---------  ----------  ----------
      4          6        40       6.80     272.00         100  100.000000
      4          7         6      42.00     252.00          15   15.000000

Quantity * 100 / 40 is all int, so 6 × 100 / 40 = 15, not 15.0. Writing 100.0 makes the whole expression decimal. Where you put the .0 matters: Quantity * 100 / 40.0 would also work, but Quantity / 40 * 100.0 would already have lost the fraction in Quantity / 40.

Dividing by zero

Division by zero stops the whole query. NULLIF(x, 0) turns a zero divisor into NULL, and dividing by NULL simply gives NULL:

1SELECT Name, Price, Stock,2       Price / NULLIF(Stock, 0) AS PricePerUnitInStock3FROM dbo.Products4WHERE ProductId IN (8, 9, 12);

Name               Price  Stock  PricePerUnitInStock
-----------------  -----  -----  -------------------
Wood screws (200)  11.90    150      0.0793333333333
Wall plugs (100)    4.50      0                 NULL
Safety goggles      6.80      0                 NULL

Other maths functions

1SELECT ABS(-4.5) AS AbsValue,2       POWER(2, 10) AS TwoToTen,3       SQRT(3.0 * 3.0 + 4.0 * 4.0) AS Hypotenuse,4       ROUND(PI() * POWER(0.15, 2), 4) AS ColumnAreaM2,5       SIGN(-7) AS SignOfMinus7;

AbsValue  TwoToTen  Hypotenuse  ColumnAreaM2  SignOfMinus7
--------  --------  ----------  ------------  ------------
     4.5      1024           5        0.0628            -1

1SELECT CEILING(2300 / 100.0) AS Boxes,2       CEILING(2300 / 100.0) * 4.50 AS Cost,3       CEILING(2300 / 100.0) * 100 - 2300 AS Spare;

Boxes    Cost  Spare
-----  ------  -----
   23  103.50      0