SQL CEILING Function (Transact SQL)

By: Kris Wenzel   |   Updated: March 6, 2022  
Works With: 

The SQL CEILING function returns the smallest integer value greater than or equal to the input value.

Description

The CEILING function evaluates the right side of the decimal value and returns an integer value that is least greater than or equal to the input value. CEILING is another SQL function for approximating numerical values like FLOOR and ROUND.

The CEILING is useful when you have a float or decimal value and you need to find the next lowest or highest integer.

CEILING and FLOOR functions explained
Explanation of CEILING and FLOOR

The CEILING is the next highest integer value; whereas, floor is the next lowest.

CEILING and FLOOR have a practical use when you’re working with discrete quantities and averages.

For instance, let’s assume the sales manager is having a sales convention for all the sales people.  They want to rent cars to be able to get around town.  Assuming each car holds 4 people how many cars do he need to rent for the business trip?

To figure this out, we can take the number of sales people and divide by four

SELECT COUNT(*) / 4.0 as NumberCars
FROM   Sales.SalesPerson

This result is 4.25 cars.  As you know you cannot rent nor drive a quarter of a car!

To get around this we need to round up to the nearest whole car.  We can use CEILING to do so.  Here is the query to use

SELECT CEILING(COUNT(*) / 4.0) as NumberCars
FROM   Sales.SalesPerson

Also, did you notice I used 4.0 rather than 4 in the calculation?  This is to ensure the result is a float; otherwise, the result is returned as an integer, and would have been implicitly converted  to an integer value of 4.

SQL CEILING Usage Notes

The only argument is a numeric value to be approximated to the smallest greater value.

The data type of input expression is from either exact numeric or approximate numeric categories. The bit data type is invalid.

The return type is the same as the input value’s data type.

Syntax

CEILING (numeric_value)

SQL CEILING Examples

The following examples explain the use of the CEILING function for positive, negative, and zero input values.

In each case an integer is returned.

  • The result for Example1 is intuitive. CEILING(22.3) is 23.
  • However, notice Example2 returns -22 and not -23. Keep in mind -22 is greater than -23, and ceiling return the next greatest integer.

The output data type is similar to the input data type, money.

The following example uses the SQL CEILING function on the AdvetureWorks2019 database column. It returns the smallest value greater than or equal to the standard cost of each product.

See Also

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}