September 7, 2021

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.

select ceiling(22.3) Example1, ceiling(-22.75) Example2, ceiling(0.0) Example3
/* Answer */
select ceiling(22.3) Example1, ceiling(-22.75) Example2, ceiling(0.0) Example3

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.

select CEILING(StandardCost) StandardCostCeiling from Production.ProductCostHistory
/* Answer */
select CEILING(StandardCost) StandardCostCeiling
from Production.ProductCostHistory

See Also

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

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

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>