CEIL()

Syntax

How it works

CEIL() takes a number and rounds it UP to the nearest whole number. CEIL(4.1) and CEIL(4.9) both return 5, because 5 is the smallest integer that is greater than or equal to the input. If the value is already a whole number, such as CEIL(4.0), it stays 4.

CEIL and CEILING are the same function. CEILING is the name defined by the ANSI SQL standard and the only name that SQL Server accepts. MySQL and PostgreSQL accept both names, so CEIL(x) and CEILING(x) are aliases there. SQLite has neither built in until version 3.35, which added CEIL and CEILING. If you need portable code across every engine, CEILING is the safer spelling.

Rounding UP is direction based, not magnitude based, so it always moves toward positive infinity. With negative numbers this moves the value closer to zero: CEIL(-1.2) returns -1, not -2, because -1 is the smallest integer that is still greater than or equal to -1.2. Compare this with FLOOR(), which rounds DOWN toward negative infinity, and ROUND(), which rounds to the nearest value in either direction.

Examples

Pages needed to show all rows

-- 1543 orders shown 50 per page needs 31 pages
SELECT CEIL(COUNT(*) / 50.0) AS pages
FROM orders;

pages
-----
   31

Round a price up to a whole unit

SELECT product,
       price,
       CEIL(price) AS rounded_up
FROM products;

product | price | rounded_up
--------+-------+-----------
 Mug    | 12.10 |        13
 Pen    |  3.99 |         4
 Book   | 20.00 |        20

CEIL with a negative value

-- Rounding up moves toward zero for negatives
SELECT CEIL(-1.2) AS a,
       CEIL(-1.9) AS b,
       CEIL( 1.2) AS c;

a  | b  | c
---+----+---
-1 | -1 | 2

Using the CEILING alias

-- CEILING is the ANSI and SQL Server name;
-- it behaves exactly like CEIL
SELECT CEILING(4.01) AS ceiling_val,
       CEIL(4.01)    AS ceil_val;

ceiling_val | ceil_val
------------+---------
          5 |        5

Batches needed to process a queue

-- how many batches of 100 to clear each queue
SELECT queue,
       job_count,
       CEIL(job_count / 100.0) AS batches
FROM queues;

queue   | job_count | batches
--------+-----------+--------
 email  |       250 |       3
 sms    |        99 |       1
 push   |       100 |       1

Common mistakes

-- ROUND rounds to the NEAREST integer,
-- so 4.10 becomes 4, not 5
SELECT ROUND(4.10) AS pages;  -- 4

-- CEIL always rounds UP, so any fraction
-- pushes to the next whole number
SELECT CEIL(4.10) AS pages;   -- 5

-- Expecting CEIL(-1.2) to give -2
-- because -2 feels like rounding up
SELECT CEIL(-1.2) AS n;  -- returns -1, not -2

-- Rounding up means toward positive infinity.
-- For negatives that is toward zero. Use FLOOR
-- if you truly want -2.
SELECT FLOOR(-1.2) AS n;  -- -2

-- CEIL is not valid on SQL Server
SELECT CEIL(price) FROM products;

-- SQL Server only ships CEILING
SELECT CEILING(price) FROM products;

Performance

CEIL() is a lightweight scalar function that runs per row with negligible cost, so it is not a performance concern on its own. The usual pitfall is wrapping an indexed column in CEIL() inside a WHERE clause: WHERE CEIL(amount) = 5 makes the predicate non sargable, so the engine cannot use an index on amount and must compute CEIL for every row.

When you divide integers to feed CEIL, remember integer division truncates first on many engines. Write CEIL(total / 50.0) rather than CEIL(total / 50) so the division happens in floating point before rounding, otherwise the result is already an integer and CEIL does nothing useful.

If you round the same computed value repeatedly, compute it once in a subquery or CTE and reuse it, rather than re-evaluating CEIL() in several places. See the indexing guide for why keeping columns bare in predicates matters.

Interview questions

SELECT CEIL(-1.2) AS n;  -- -1

SELECT CEIL(COUNT(*) / 50.0) AS pages
FROM orders;