Querying and aggregation
Numeric operations and conversions
Types can change the result of seemingly correct arithmetic.
Understand the concept
ROUND rounds, ABS returns an absolute value, and CAST/CONVERT change types. CONVERT includes SQL Server-specific formatting styles. TRY_CAST/TRY_CONVERT return NULL for supported conversions that fail; they do not magically permit every forbidden conversion or remove all length limits.
Integer division can discard the fractional part. Convert the numerator to decimal before dividing. NULLIF(x, 0) turns a zero denominator into NULL, so handle the resulting NULL deliberately. Report failed conversions instead of merely hiding them behind TRY_CONVERT.
Use it for ratios, amounts, imports and validation.
Converting 5/2 to decimal after integer division and expecting to recover the fraction.
One topic. Three levels of depth.
SELECT ROUND(CAST(19.876 AS decimal(10,3)), 2) AS Rounded;The numeric value is 19.88; the type may still display three decimal places.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Divide 18 lines by 12 orders to get average lines per order.
Show the solution and explanation
SELECT CAST(18 AS decimal(10,2)) / 12 AS AverageLines;The result is 1.5. The fixture confirms that every order has a line.