The ceiling() function returns the smallest integer greater than, or equal to, the specified numeric expression.
SQL CEILING()
SQL CEILING() is a built-in function used to return the smallest integer value that is greater than or equal to a number given as input. The ceiling() function returns the smallest integer value greater than or equal to a number.
Syntax
SELECT CEILING (Number);
Parameters
Number: The number whose ceiling value is to be calculated.
Examples
Query 1
SELECT CEILING (2.5);
Output
3
Explanation
Here, the ceiling value is returned.
Query 2
SELECT CEILING (1.3);
Output
2
Explanation
Here, the ceiling value is returned.
Query 3
SELECT CEILING (-1.3);
Output
-1
Explanation
Here, the ceiling value is returned, which is negative.
Do not confuse why the output is not -2. This is because, like the already mentioned function returns, the value is greater or equal to a number, so -1 is greater than -1.3 so, the output is -1.
Query 4
SELECT CEILING (25.75 + 5.0);
Output
31
Explanation
Here, the ceiling value of the expression is returned.
Above were the common examples to illustrate the meaning of the CEILING function.
Let’s apply this function on a table Employee.
Employee
Emp_id | First_name | City | State | Salary_per_month |
101 | Rohit | Patna | Bihar | 25895.75 |
201 | Shivam | Jalandhar | Punjab | 30122.45 |
301 | Karan | Allahabad | Uttar Pradesh | 99872.15 |
401 | Suraj | Kolkata | West Bengal | 88789.88 |
501 | Akash | Vizag | Andhra Pradesh | 98985.77 |
Suppose we want to remove the decimal point from the salary_per_month, then the ceiling function can be applied to it.
Query
Select Emp_id, First_name, City, State, CEILING (Salary_per_month) AS Salary_per_month from Employee;
Output
Emp_id | First_name | City | State | Salary_per_month |
101 | Rohit | Patna | Bihar | 25896 |
201 | Shivam | Jalandhar | Punjab | 30123 |
301 | Karan | Allahabad | Uttar Pradesh | 99873 |
401 | Suraj | Kolkata | West Bengal | 88790 |
501 | Akash | Vizag | Andhra Pradesh | 98986 |
Explanation
Here, you can see that salary_per_month values previously contained decimal values have been removed.
MySQL CEILING()
The CEILING() function in MySQL returns the smallest integer value bigger than or equal to a number.
PostgreSQL CEILING()
The PostgreSQL ceiling() returns the value after rounding up any positive or negative decimal value as greater than the argument.
Conclusion
In SQL Server (Transact-SQL), the CEILING function returns the smallest integer value greater than or equal to a number.