🔹SQL Server Math Functions — ROUND(), ABS(), CEILING(), FLOOR() Explained (With Examples)

---

1) Quick overview

• ROUND(): Round a numeric expression to a specified precision (can also truncate).

• ABS(): Return the absolute (non-negative) value of a numeric expression.

• CEILING(): Return the smallest integer greater than or equal to the input (always rounds up).

• FLOOR(): Return the largest integer less than or equal to the input (always rounds down).

These functions are available in T‑SQL (SQL Server, Azure SQL DB) and are indispensable for reporting, finance calculations, and data transformations.

---

2) ROUND() — syntax, examples, and tips

Syntax

ROUND(numeric_expression, length [, function])

• numeric_expression: the value to round (FLOAT, DECIMAL, MONEY, etc.).

• length: number of decimal places to round to. Can be negative to round left of the decimal.

• function: optional. If 0 or omitted → standard rounding; if 1 → truncate (no rounding).

Examples

SELECT ROUND(123.4567, 2) AS RoundedTo2; -- 123.46

SELECT ROUND(123.4567, 0) AS RoundedTo0; -- 123

SELECT ROUND(123.4567, -1) AS RoundedTo10; -- 120

SELECT ROUND(123.4567, 2, 1) AS Truncated2; -- 123.45 -- truncates instead of rounding

Edge cases / notes

• If length is negative and larger than the number of digits left of the decimal, the result can be 0.

• ROUND() preserves numeric scale/type depending on input types (DECIMAL keeps precision better than FLOAT).

• To deliberately truncate (drop trailing decimals without rounding), use the third argument 1.

Use-case: rounding currency values to 2 decimal places for display, or rounding averages in reports.

---

3) ABS() — absolute value

Syntax

ABS(numeric_expression)

Examples

SELECT ABS(-10) AS Pos1; -- 10

SELECT ABS(25) AS Pos2; -- 25

SELECT ABS(-123.45) AS Pos3; -- 123.45

Use-case: measuring magnitude of differences (e.g., ABS(Sales - Cost)), distance calculations, or when you only care about magnitude, not direction.

Tip: ABS() does not change zero or positive values; it returns the same data type as the input when possible.

---

4) CEILING() and FLOOR() — always up, always down

CEILING — smallest integer greater than or equal to the expression.

Syntax

CEILING(numeric_expression)

Examples

SELECT CEILING(123.45) AS Ceil1; -- 124

SELECT CEILING(10.00) AS Ceil2; -- 10

SELECT CEILING(-123.45) AS Ceil3; -- -123 -- note how "up" behaves with negatives

FLOOR — largest integer less than or equal to the expression.

Syntax

FLOOR(numeric_expression)

Examples

SELECT FLOOR(123.45) AS Floor1; -- 123

SELECT FLOOR(10.99) AS Floor2; -- 10

SELECT FLOOR(-123.45) AS Floor3; -- -124 -- note how "down" behaves with negatives

Important: For negative numbers CEILING() and FLOOR() move in different directions relative to zero:

• CEILING(-2.3) → -2 (moves up toward zero)

• FLOOR(-2.3) → -3 (moves down away from zero)

Use-case: CEILING() for computing required counts (e.g., how many pages are needed), FLOOR() for bucketizing values into integer thresholds.

---

5) Comparison & behavior with negative numbers

Function	What it returns (example)	Notes on negative values
ROUND(x, n)	Rounded value to n decimals	Rounds normally; can also truncate with 3rd param
ABS(x)	Absolute value	Removes sign
CEILING(x)	Smallest integer >= x	CEILING(-2.3) → -2
FLOOR(x)	Largest integer <= x	FLOOR(-2.3) → -3

Examples in one query

SELECT

3.5 AS Value,

ROUND(3.5,0) AS Round_3_5,

CEILING(3.5) AS Ceiling_3_5,

FLOOR(3.5) AS Floor_3_5,

ABS(-3.5) AS Abs__Neg3_5;

-- Expected output row: 3.5 | 4 | 4 | 3 | 3.5

Rounding of .5 values: SQL Server’s ROUND() uses standard arithmetic rounding (commonly: round .5 away from zero). If your application demands banker's rounding (round half to even), implement a custom function or handle it in application code.

---

6) Real-world examples and patterns

A. Currency display (round to 2 decimals)

SELECT ProductId, Price, ROUND(Price, 2) AS Price_Display

FROM Products;

B. Bill generation (always charge full unit)

-- Charge full rupee/dollar if there is any fraction

SELECT OrderId, Price, CEILING(Price) AS AmountToBill

FROM Orders;

C. Page count (how many pages for N rows)

DECLARE @rows INT = 1023; DECLARE @pageSize INT = 100;

SELECT CEILING(@rows * 1.0 / @pageSize) AS PagesNeeded; -- 11 pages

D. Absolute difference (profit/loss magnitude)

SELECT OrderId, ABS(Revenue - Cost) AS DiffMagnitude

FROM OrderFinance;

E. Bucketing / grouping (floor)

-- Price buckets of size 10

SELECT FLOOR(Price / 10) * 10 AS PriceBucket, COUNT(*)

FROM Products

GROUP BY FLOOR(Price / 10);

---

7) Common pitfalls & best practices

• Floating point precision: Avoid FLOAT for exact money calculations — prefer DECIMAL(18,2) to prevent binary-floating precision surprises.

• Rounding ties: If you must use bankers rounding (round half to even) for financial accuracy, implement it explicitly — SQL Server’s built-in ROUND() follows arithmetic rounding conventions by default.

• Truncation vs rounding: Use ROUND(..., function = 1) when you want to truncate without rounding.

• NULL values: These functions return NULL if the input expression is NULL — handle with ISNULL() or COALESCE() if needed.

• Performance: Numeric functions are cheap, but avoid applying them in large-scale JOIN/WHERE predicates on columns without indexing — consider persisted computed columns if frequently used for filtering.

---

Below are the key authoritative sources I used to verify the factual points in the article (useful to cite or keep for reference):

• ROUND() official docs. Microsoft Learn

• ABS() official docs. Microsoft Learn

• CEILING() official docs. Microsoft Learn

• FLOOR() official docs. Microsoft Learn