🔹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

DELETE vs TRUNCATE vs DROP in SQL Server

 1. DELETE

• Used to remove rows one at a time from a table.

• Can be filtered with WHERE clause.

• Each deleted row is logged in transaction log (slower).

• Identity column value does not reset unless explicitly used with DBCC CHECKIDENT.

• Can be rolled back if inside a transaction.

✅ Example:

DELETE FROM Employees WHERE DeptId = 2;

---

2. TRUNCATE

• Removes all rows from a table (no WHERE).

• Logs only page deallocations (faster than DELETE).

• Resets identity column to seed value.

• Cannot be used if:

  • Table is referenced by a foreign key.

  • Indexed views exist on the table.

• Can be rolled back if inside a transaction.

✅ Example:

TRUNCATE TABLE Employees;

---

3. DROP

• Deletes the entire table structure and data.

• Table definition, constraints, indexes, triggers — all removed.

• Cannot be rolled back (once committed).

• After DROP, table is gone from database; must be recreated to use again.

✅ Example:

DROP TABLE Employees;

---

4. Key Differences Table

Feature	DELETE	TRUNCATE	DROP
Removes	Specific rows (WHERE) or all	All rows	Entire table (structure + data)
WHERE Clause	✅ Allowed	❌ Not allowed	❌ Not applicable
Logging	Row-by-row (slow)	Minimal logging (fast)	Logs table removal
Identity Reset	❌ No	✅ Yes	❌ Not applicable
Rollback Possible	✅ Yes	✅ Yes	❌ No (unless inside transaction)
Foreign Key Restriction	✅ Allowed	❌ Not allowed if referenced	✅ Allowed (but drops constraints)
Table Structure Exists?	✅ Yes (after delete)	✅ Yes (empty table)	❌ No (removed completely)
Speed	Slower (row-by-row)	Faster (deallocate pages)	Fastest (drops object)

---

5. When to Use?

• DELETE → When you need to remove specific rows or want triggers to fire.

• TRUNCATE → When you want to remove all rows quickly and reset identity.

• DROP → When you want to completely remove table and free space.

---

6. Interview Tip

If interviewer asks:
👉 “Can we rollback TRUNCATE?”
✅ Answer: Yes, TRUNCATE can be rolled back if executed inside a transaction.

BEGIN TRAN;
TRUNCATE TABLE Employees;
ROLLBACK; -- restores data

👉 “Why TRUNCATE is faster than DELETE?”
✅ Answer: Because TRUNCATE logs only extent (page) deallocation, while DELETE logs each row removal.